cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
hxkresl Member
Member

TopN ties: handling ties when using visual level filters

I want to use the visual filters to produce Top 5 but since there are many ties visual shows about 20'things'. So, i have used DAX to create a calculated table to create the desired Top 5 visual. Problem with DAX based visual is that drill down doesn't expose records.

 

so, any way to handle ties in the visual level filters?

8 REPLIES 8
Microsoft v-ljerr-msft
Microsoft

Re: TopN ties: handling ties when using visual level filters

Hi @hxkresl,



So, i have used DAX to create a calculated table to create the desired Top 5 visual. Problem with DAX based visual is that drill down doesn't expose records.

I'm not sure I understand this. Could you be more precisely with your problem by posting your table structures with some sample data and your expected result? So that we can better assist on it. Smiley Happy

 

Regards

hxkresl Member
Member

Re: TopN ties: handling ties when using visual level filters

hi @v-ljerr-msft

controlling ties with DAX when using topN below:

Top5ToolsByAlertCount = TOPN
(5,
SUMMARIZE(
mc_applications
,mc_applications[mc_applicationid],AppbyAlertCount[RankAppbyAlertCountcol],"CountOfAlertsByApp", COUNT(mc_alerts[mc_alertid])
),AppbyAlertCount[RankAppbyAlertCountcol],ASC,"CountOfAlertsByApp",ASC,mc_applications[mc_applicationid],DESC)

and then createing visual using DAX attributes

Capture4.PNG

 

results.   See only 5 bars.

Capture.PNG

 

When just pulling Applicationname (aka Tool) and count of Alerts into visual,

Capture1.PNG

 

Since there are many ties for 5th place I have more than 5 bar. I have no idea how to control for ties using what's available in pbi workspace.  

Capture2.PNG

 

Do you know how?

 

Microsoft v-ljerr-msft
Microsoft

Re: TopN ties: handling ties when using visual level filters

Hi @hxkresl,

If there is a tie, in order_by values, at the N-th row of the table, then all tied rows are returned. Then, when there are ties at the N-th row the function might return more than n rows.

I don't think there is an option to change this default behavior for TopN filter in Power BI currently. And I also cannot find a alternative way in this scenario. So I would suggest you add it as an idea on Power BI Ideas to improve Power BI on this feature. Smiley Happy

 

Regards

hxkresl Member
Member

Re: TopN ties: handling ties when using visual level filters

@v-ljerr-msft  Since my opening post wasn't clear what the issue was without a demo will you please suggest a problem statement that will resonate with others?  I will then post to the suggestions forums.

 

Dennes Frequent Visitor
Frequent Visitor

Re: TopN ties: handling ties when using visual level filters

 

If you repeat the TOPN again, it will remove the ties.

 

It will be like this:

 

Top5ToolsByAlertCount = TOPN(5;TOPN
(5,
SUMMARIZE(
mc_applications
,mc_applications[mc_applicationid],AppbyAlertCount[RankAppbyAlertCountcol],"CountOfAlertsByApp", COUNT(mc_alerts[mc_alertid])
),AppbyAlertCount[RankAppbyAlertCountcol],ASC,"CountOfAlertsByApp",ASC,mc_applications[mc_applicationid],DESC))

 

Dennes Frequent Visitor
Frequent Visitor

Re: TopN ties: handling ties when using visual level filters

 

If you repeat the TOPN again, it will remove the ties.

 

It will be like this:

 

Top5ToolsByAlertCount = TOPN(5;TOPN
(5,
SUMMARIZE(
mc_applications
,mc_applications[mc_applicationid],AppbyAlertCount[RankAppbyAlertCountcol],"CountOfAlertsByApp", COUNT(mc_alerts[mc_alertid])
),AppbyAlertCount[RankAppbyAlertCountcol],ASC,"CountOfAlertsByApp",ASC,mc_applications[mc_applicationid],DESC))

 

hxkresl Member
Member

Re: TopN ties: handling ties when using visual level filters

thanks @Dennes, helpful for DAX based top 5. I am glad to see how it is done in DAX/with Summarize, althought this post was about handling ties in the desktop.  Drawback with DAX/Summarize method is that drill down in the Data/Drill tab will not return much meaningful granular data.  For this reason would like way to handle ties using visual level filters.

vgarlap Member
Member

Re: TopN ties: handling ties when using visual level filters

Hi, 

 

Where do we need to change this code ? 

Helpful resources

Announcements
New Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

November 2019 Community Highlights

November 2019 Community Highlights

Get an overview of the events and great community content from November.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)