Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
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
Refer the below snap for the expected output
Source Data
Expected Output
Please let me if you need anything
@Anonymous
Ok
So we have to consider the first status per each id? Please clarify further
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
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
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
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.
@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
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 )
)
@Anonymous
Please let me know what is the issue you are facing. I might be able to support you.
@Anonymous
No. But you can use COUNTROWS ( DISTINCT ( ) )
@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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
22 | |
12 | |
8 |
User | Count |
---|---|
76 | |
50 | |
46 | |
16 | |
12 |