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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

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!

 

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

 

Anonymous
Not applicable

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.

 

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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