# Count by status on latest plan in SSAS DAX

1 Reputation point
2022-03-10T07:16:06.24+00:00

Hi Team,
I need to create measure based on latest status. I tried dax query in ssas (SSMS) but not getting expected results.
Logic: We need to calculate count by each status on latest plan.
for example: employee (5245) is having two plans (8869,6988)
when we calculate status for accepted then we have to consider this empid becauase latest planid(8869) staus is accepted .i.e. this eid should consider status: ACCEPTED only because it has the latest planid.

Source Data

Output Expected

Query :SSAS DAX in SSMS

Define
MEASURE table[AcceptedCount] =
COUNTROWS (
FILTER (
SUMMARIZE ( table,table[EmployeeNumber],table[status]),
table[status] = "ACCEPTED"
))
MEASURE table[EscalatedCount] =
COUNTROWS (
FILTER (
SUMMARIZE ( table,table[EmployeeNumber],table[status]),
table[status] = "ESCALATED"
))
MEASURE table[overall] =
COUNTROWS (
FILTER (
SUMMARIZE ( table,table[EmployeeNumber],table[status]),
table[status] in {"ESCALATED","ACCEPTED"}
))
VAR status_count =
SUMMARIZECOLUMNS (
table[unit],
"AcceptedCount_status", table[AcceptedCount],
"EscalatedCount_status", table[EscalatedCount]
"Overallcount",table[overall]
)
evaluate status_count

Currently returning output with my query

SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,249 questions

1. 33,386 Reputation points
2022-03-11T02:00:47.81+00:00

Hi

``````Define
MEASURE table[AcceptedCount] =
COUNTROWS (
FILTER (
SUMMARIZE ( table,table[EmployeeNumber],table[status]),
table[status] = "ACCEPTED"
))
MEASURE table[EscalatedCount] =
COUNTROWS (
FILTER (
SUMMARIZE ( table,table[EmployeeNumber],table[status]),
table[status] = "ESCALATED"
))
MEASURE table[overall] =
COUNTROWS (
FILTER (
SUMMARIZE ( table,table[EmployeeNumber],table[status]),
table[status] in {"ESCALATED","ACCEPTED"}
))
VAR status_count =
Filter(SUMMARIZECOLUMNS (
table[unit],
table[EmployeeNumber],
table[status],
"AcceptedCount_status", table[AcceptedCount],
"EscalatedCount_status", table[EscalatedCount],
"Overallcount",Calcualte(table[overall])
), table[status]= "ACCEPTED")
evaluate status_count
``````

Regards，

Zoe