Count by status on latest plan in SSAS DAX

Question

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

Thanks in Advance

Answer 1

Hi

Please check below code to see if it is helpful.

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

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.