COUNT of one column based of MAX of another

[a_chhibber]

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

[Anonymous · Accepted 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

[a_chhibber]

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]

HI @a_chhibber

Use this formula in Modeling -> New column.

Thanks
Raj

[a_chhibber]

@Anonymous, I was trying to use it as a measure. It worked now. Thanks again!!