Count by status on latest plan in SSAS DAX

ks 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

unit eid planid Status  
CATEGORY 5245 8869 ACCEPTED  
CATEGORY 5245 6988 ESCALATED

Output Expected

181739-image.png

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

181720-image.png

Thanks in Advance

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
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. ZoeHui-MSFT 33,386 Reputation points
    2022-03-11T02:00:47.81+00:00

    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.

    0 comments No comments