Forum Discussion

hxkresl's avatar
hxkresl
Icon for Advocate III rankAdvocate III
9 years ago

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. Pro...
Need Help
hxkresl's avatar
hxkresl
Icon for Advocate III rankAdvocate III
9 years ago

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

 

results.   See only 5 bars.

 

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

 

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.  

 

Do you know how?

 

Dennes's avatar
Dennes
Frequent Visitor
9 years ago

 

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's avatar
    hxkresl
    Icon for Advocate III rankAdvocate III
    9 years ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi, 

     

    Where do we need to change this code ?