count latest plan by each status in ssas DAX

[Anonymous]

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

unit	eid		Status	[AcceptedCount_Status]	[EscalatedCount_Status]	[OverallCount]
CATEGORY	5245		ACCEPTED	1		1

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

unit	eid		Status	[AcceptedCount_Status]	[EscalatedCount]_Status	[OverallCount]
CATEGORY	5245		ACCEPTED	1	1	2

Thanks in Advance

[tamerj1]

Hi @Anonymous 
You may try the following

DEFINE
    MEASURE table[AcceptedCount] =
        COUNTROWS ( CALCULATETABLE ( table, table[status] = "ACCEPTED" ) )
    MEASURE table[EscalatedCount] =
        COUNTROWS ( CALCULATETABLE ( table, table[status] = "ESCALATED" ) )
    MEASURE table[overall] =
        COUNTROWS (
            CALCULATETABLE ( table, table[status] IN { "ESCALATED", "ACCEPTED" } )
        )
    VAR status_count =
        SUMMARIZECOLUMNS (
            table[unit],
            "AcceptedCount_status", [AcceptedCount],
            "EscalatedCount_status", [EscalatedCount],
            "Overallcount", [overall]
        )

EVALUATE
status_count

[Anonymous]

Hi @tamerj1,

The query  which you given is resulting count as '1' in both accepted and escalated like below but this is not expected

For ex:  we need to return onlymax planid status of emp

1. 5245 emp is having two planids 8869,6988 in that maxplanid is:8869 and sttaus of that max planid is : Accepted then we have to return count as "1" in accepted count. empty in escalated count column
2. 7676emp is having two planids 9898,9079 in that maxplanid is:9898 and sttaus of that max planid is : Esclated then we have to return count as "1" in Esclated count. empty in Accepted count column

Refer the below snap for the expected output

1. 

Source Data

Expected Output

Please let me if you need anything

[tamerj1]

@Anonymous 
Ok 
So we have to consider the first status per each id? Please clarify further

[tamerj1]

Let me clarify my concer further. 
For CATEGORY - 5245 we have both "ACCEPTED" and "EXCALATED" but you want to select "ACCEPTED". That's fine but on which bases?
Same for Channel - 7676 we have both "ACCEPTED" and "EXCALATED" but you want to select "EXCALATED"... On which bases?

[Anonymous]

Hi @tamerj1 ,

Based on the maxium planid column

[tamerj1]

@Anonymous 

Here you go

DEFINE
    MEASURE table[AcceptedCount] =
        VAR MaxPanID =
            MAX ( table[planid] )
        RETURN
            COUNTROWS (
                CALCULATETABLE ( table, table[status] = "ACCEPTED", table[planid] = MaxPanID )
            )
    MEASURE table[EscalatedCount] =
        VAR MaxPanID =
            MAX ( table[planid] )
        RETURN
            COUNTROWS (
                CALCULATETABLE ( table, table[status] = "ESCALATED", table[planid] = MaxPanID )
            )
    VAR status_count =
        SUMMARIZECOLUMNS (
            table[unit],
            "AcceptedCount_status", [AcceptedCount],
            "EscalatedCount_status", [EscalatedCount],
            "Overallcount", [AcceptedCount] + [EscalatedCount]
        )

EVALUATE
status_count

[Anonymous]

Hi @tamerj1 ,

Please modify the query according to this requirement.

Source Data:

Expected Output

But i'm getting below output with these query

Please let me know if required anything

[tamerj1]

HI @Anonymous 
You can just add "eid" to the filter context 

DEFINE
    MEASURE source[AcceptedCount] =
        VAR MaxPanID =
            MAX ( source[planid] )
        RETURN
            COUNTROWS (
                CALCULATETABLE ( source, source[status] = "ACCEPTED", source[planid] = MaxPanID )
            )
    MEASURE source[EscalatedCount] =
        VAR MaxPanID =
            MAX ( source[planid] )
        RETURN
            COUNTROWS (
                CALCULATETABLE ( source, source[status] = "ESCALATED", source[planid] = MaxPanID )
            )
    VAR status_count =
        SUMMARIZECOLUMNS (
            source[unit],
            source[planid],
            "AcceptedCount_status", [AcceptedCount],
            "EscalatedCount_status", [EscalatedCount],
            "Overallcount", [AcceptedCount] + [EscalatedCount]
        )

EVALUATE
status_count

Please let me know If you don't want to show "planid" in the output of the query.

[tamerj1 · Accepted solution]

@Anonymous 
This query should work without having to add "PlanID" to the report

DEFINE
    MEASURE source[AcceptedCount] =
        SUMX (
            VALUES ( Source[eid] ),
            VAR MaxPlanID =
                CALCULATE ( MAX ( Source[planid] ) )
            RETURN
                CALCULATE (
                    COUNTROWS ( SOURCE ),
                    Source[planid] = MaxPlanID,
                    Source[status] = "ACCEPTED"
                )
        )
    MEASURE source[EscalatedCount] =
        SUMX (
            VALUES ( Source[eid] ),
            VAR MaxPlanID =
                CALCULATE ( MAX ( Source[planid] ) )
            RETURN
                CALCULATE (
                    COUNTROWS ( SOURCE ),
                    Source[planid] = MaxPlanID,
                    Source[status] = "ESCALATED"
                )
        )
    VAR status_count =
        SUMMARIZECOLUMNS (
            source[unit],
            "AcceptedCount_status", [AcceptedCount],
            "EscalatedCount_status", [EscalatedCount],
            "Overallcount", [AcceptedCount] + [EscalatedCount]
        )

EVALUATE
status_count

[Anonymous]

Hi @tamerj1 ,

 Can we write count/distinct count instead of countrows in measure expressions?

ex: 

MEASURE table[AcceptedCount] =

        VAR MaxPanID =

            MAX ( table[planid] )

        RETURN

            COUNT (

                CALCULATETABLE ( table, table[status] = "ACCEPTED", table[planid] = MaxPanID )

            )

[tamerj1]

@Anonymous 

Please let me know what is the issue you are facing. I might be able to support you. 

[tamerj1]

@Anonymous 

No. But you can use COUNTROWS ( DISTINCT ( ) )

[tamerj1]

@Anonymous 
Actually after a second look at your requirement I noticed something which is not clear. You are summarizing by status. Therefore, in all cases for "overall" you will either get the count for "Accepted" or the count of 'Escalated". If your goal is just to merge the counts of both "Accepted" and 'Escalated" to have one column without gaps, then you can do the following

DEFINE
    MEASURE table[AcceptedCount] =
        COUNTROWS ( CALCULATETABLE ( table, table[status] = "ACCEPTED" ) )
    MEASURE table[EscalatedCount] =
        COUNTROWS ( CALCULATETABLE ( table, table[status] = "ESCALATED" ) )
    VAR status_count =
        SUMMARIZECOLUMNS (
            table[unit],
            "AcceptedCount_status", [AcceptedCount],
            "EscalatedCount_status", [EscalatedCount],
            "Overallcount", [AcceptedCount] + [EscalatedCount]
        )

EVALUATE
status_count