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.
I am having problem calculating count of one column, based on MAX of another column.
I have to get a count of ticket IDs having score as "good", but only consider the rows with MAX created_at dates.
So, in this case, for the first ticket_id, the valuse will be "bad", and my count should have only 3 ticket_ids with "good" score. Can someone please guide me to the right DAX for the measure?
TIA
Solved! Go to Solution.
Hi @a_chhibber
Try this formula, Cnt_Max is the table name i have used.
Max_cnt = CALCULATE(COUNT(Cnt_Max[Score]), FILTER(Cnt_Max,Cnt_Max[Ticket_id]=EARLIER(Cnt_Max[Ticket_id])), FILTER(Cnt_Max,Cnt_Max[Score]="Good"), FILTER(Cnt_Max, Cnt_Max[Date]=CALCULATE(MAX(Cnt_Max[Date]), FILTER(Cnt_Max,Cnt_Max[Ticket_id]=EARLIER(Cnt_Max[Ticket_id])))) )
Thanks
Raj
Hi @a_chhibber
Try this formula, Cnt_Max is the table name i have used.
Max_cnt = CALCULATE(COUNT(Cnt_Max[Score]), FILTER(Cnt_Max,Cnt_Max[Ticket_id]=EARLIER(Cnt_Max[Ticket_id])), FILTER(Cnt_Max,Cnt_Max[Score]="Good"), FILTER(Cnt_Max, Cnt_Max[Date]=CALCULATE(MAX(Cnt_Max[Date]), FILTER(Cnt_Max,Cnt_Max[Ticket_id]=EARLIER(Cnt_Max[Ticket_id])))) )
Thanks
Raj
Thanks @Anonymous. But I am gettin the error "EARLIER / EARLIEST refers to an earlier row context which doesn't exist", with the following par underlined.
Max_cnt = CALCULATE(COUNT(Cnt_Max[Score]), FILTER(Cnt_Max,Cnt_Max[Ticket_id]=EARLIER(Cnt_Max[Ticket_id])), FILTER(Cnt_Max,Cnt_Max[Score]="Good"), FILTER(Cnt_Max, Cnt_Max[Date]=CALCULATE(MAX(Cnt_Max[Date]), FILTER(Cnt_Max,Cnt_Max[Ticket_id]=EARLIER(Cnt_Max[Ticket_id])))) )
@Anonymous, I was trying to use it as a measure. It worked now. Thanks again!!
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 |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |