Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
bondfranco
Frequent Visitor

Top N reason for the month

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.

1 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.

View solution in original post

4 REPLIES 4
lbendlin
Super User
Super User

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.

Helpful resources

Announcements
October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors