Top N reason for the month

[bondfranco]

Hi, I need to display Top N reason for the month based to below data model in power BI. Tables: Date ([fulldate,date],[ReportMonth varchar(50) format mmmyy]) ,Reason ([ReasonDate,date],[Reason varchar(500)] The top filter should display top 3 reasons for the month using DAX.It should not display the top 3 reasons on overall count. The filter will be on ReportMonth column to find the top 3 reasons.Appreciate any help.

[lbendlin]

Top 3 reasons by what?  count ?  another field?

[bondfranco]

Hi,

Those are by count of reason for a particular month.

[lbendlin]

This works for me:

Measure = 
var m=SELECTEDVALUE('Table'[Month])
var v=SUMMARIZE(FILTER(ALLSELECTED('Table'),'Table'[Month]=m),'Table'[Reason],"Ct",count('Table'[Reason]))
var t=TOPN(3,v,[Ct],DESC)
var r= CONCATENATEX(t,[Reason],",")
return r

[bondfranco · Accepted solution]

Hi All,

I used the Intersect with values on the DAX measure for both of the above tables and it worked for me.

ReasonCount=Calculate(Count(Reason),Intersect(Values(Reason),Values(Date)))

Be careful with the table sequence in intersect they incur different results for different combination.Intersect(T1,T2) is not equal to Intersect(T2,T1).

Thanks.