topic Re: count latest plan by each status in ssas DAX in DAX Commands and Tips

count latest plan by each status in ssas DAX

Anonymous — Thu, 10 Mar 2022 07:07:25 GMT

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

Re: count latest plan by each status in ssas DAX

tamerj1 — Thu, 10 Mar 2022 11:43:33 GMT

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

Re: count latest plan by each status in ssas DAX

tamerj1 — Thu, 10 Mar 2022 11:54:34 GMT

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

Re: count latest plan by each status in ssas DAX

Anonymous — Thu, 10 Mar 2022 13:00:03 GMT

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

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
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

Source Data

Expected Output

Please let me if you need anything

Re: count latest plan by each status in ssas DAX

tamerj1 — Thu, 10 Mar 2022 13:11:12 GMT

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

Re: count latest plan by each status in ssas DAX

tamerj1 — Thu, 10 Mar 2022 13:19:30 GMT

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?

Re: count latest plan by each status in ssas DAX

Anonymous — Thu, 10 Mar 2022 14:10:45 GMT

Hi tamerj1 ,

Based on the maxium planid column

Re: count latest plan by each status in ssas DAX

tamerj1 — Thu, 10 Mar 2022 14:36:11 GMT

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

Re: count latest plan by each status in ssas DAX

Anonymous — Thu, 10 Mar 2022 18:42:21 GMT

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 )

)

Re: count latest plan by each status in ssas DAX

tamerj1 — Thu, 10 Mar 2022 18:56:46 GMT

Anonymous

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

Re: count latest plan by each status in ssas DAX

tamerj1 — Thu, 10 Mar 2022 19:15:32 GMT

Anonymous

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

Re: count latest plan by each status in ssas DAX

Anonymous — Tue, 15 Mar 2022 07:57:42 GMT

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

Re: count latest plan by each status in ssas DAX

tamerj1 — Tue, 15 Mar 2022 08:54:02 GMT

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.

Re: count latest plan by each status in ssas DAX

tamerj1 — Tue, 15 Mar 2022 13:31:37 GMT

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