Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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.
Solved! Go to 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.
Top 3 reasons by what? count ? another field?
Hi,
Those are by count of reason for a particular month.
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
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.
User | Count |
---|---|
30 | |
19 | |
15 | |
14 | |
10 |