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. Problem with DAX based visual is that drill down doesn't expose records.

 

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

Need Help

10 Replies

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    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. :smileyhappy:

     

    Regards

    • 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?

       

      • krogers's avatar
        krogers
        Regular Visitor
        6 years ago

        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.