cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
hxkresl
Helper V
Helper V

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?

10 REPLIES 10
v-ljerr-msft
Microsoft
Microsoft

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

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?

 

The best way I've found to handle this is to create another version of your metric that eliminates ties by including small fractions.  You can use this new column in the Top N filter to successfully limit to Top 5 even if there are ties, but the values displayed in the visual will not change. 

 

Step 1) Create a custom column that replicates your metric formula.  Add to your formula an ID or Index column (any column that has a unique numeric value per each item that you are ranking) multiplied by a small number such as .000000000000001.  This will cause it to break ties successfully but will not change the ranking.  Make sure the fraction multiplied is small enough that it does not change the aggregate numbers when summed up in the visualization.  If there are specific requirements around how ties should be broken, you could add logic to this formula to do it accordinly.  Multiplying by an ID or index value will probably break the tie arbitrarily (which seems fine for this use case).

 

New Column For Ranking = Original metric + (IDField *.000000000000001)

 

In OP's example: 

CountofAlertsByAppForRanking = COUNT(mc_alerts[mc_alertid]) + (mc_applications[mc_applicationid] * .000000000000001)

 

Step 2) Add the new custom column to the "by value" area in the Top N filter. 

 

The chart should appear exactly the same except it will be cut off at the Top N number rather than displaying multiple rows in the case of ties.  It should also handle drill-down just fine.  

This is a really clever solution -- thank you for sharing!

Dennes
Frequent Visitor

 

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))

 

Hi, 

 

Where do we need to change this code ? 

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.

Dennes
Frequent Visitor

 

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))

 

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

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

 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.