The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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!!