topic Re: Top N reason for the month in DAX Commands and Tips

Top N reason for the month

bondfranco — Fri, 03 Jul 2020 11:06:51 GMT

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.

Re: Top N reason for the month

lbendlin — Fri, 03 Jul 2020 15:07:46 GMT

Top 3 reasons by what? count ? another field?

Re: Top N reason for the month

bondfranco — Fri, 03 Jul 2020 16:23:41 GMT

Hi,

Those are by count of reason for a particular month.

Re: Top N reason for the month

lbendlin — Fri, 03 Jul 2020 19:35:21 GMT

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

Re: Top N reason for the month

bondfranco — Wed, 08 Jul 2020 10:53:41 GMT

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.