Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Top 5 filters

When i was applied a top 5 filters in below garph, It is showing the values are more than 10. Since y axis values are same . In this case, how can i get only top 5 records and how to apply in powerbi.

 

Need Help

7 Replies

  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    Hi , Anonymous 

    According to your description, you are using the "TOPN" in filter on this visual .And when the value is the same it will show the all the value is the same in your visual. But you want to just show the 5 .

    Here are the steps you can refer to :
    (1)This is my test data:

     

    For your need , we need to first to create a calculated column to distinct abount the x-axis.

    Column = RANKX('Table','Table'[Type],,ASC,Dense)

    (2)Then we need to create two measures like this:

    Measure = RANKX( ALLSELECTED('Table'[Type]) , CALCULATE(SUM('Table'[Value])  ,ALLEXCEPT('Table','Table'[Type])) ,,DESC,Skip)
    Measure 2 = var _t =ADDCOLUMNS( ALLSELECTED('Table'[Type],'Table'[Column]) , "rank" , [Measure])
var  _t2 = ADDCOLUMNS(_t , "flag" ,IF( COUNTROWS( FILTER(_t , [rank]=EARLIER([rank]) && 'Table'[Column]<EARLIER([Column])))  =BLANK() , [rank] , COUNTROWS( FILTER(_t , [rank]=EARLIER([rank]) && 'Table'[Column]<EARLIER([Column])))+[rank]      ))
return 
MAXX(FILTER(_t2,[Type]=MAX('Table'[Type])) , [flag])

     

    Then we can put the [Measure 2] on the "Filter on this visual" and then we can meet your need:

     

     

     

     

    If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem. (You can also upload you sample .pbix [without sensitive data] to the OneDrive and share with the OneDrive link to me ! )

    Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

     

    Best Regards,

    Aniya Zhang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      I have done with same steps. result is not getting as expected. please check below screenshot

       

      • v-yueyunzh-msft's avatar
        v-yueyunzh-msft
        Icon for Community Support rankCommunity Support
        3 years ago

        Hi , Anonymous 

        Thanks for your quick response! I test it in my side , it works .And it is very hard to see the issue according to the image. Can you share the sample pbix for me to explore the issue?

        And first you can check this:
        (1)The calculated is return right : grouped by the [Type] Column ( in  your side is the [MECHAINES] column )

        (2)Then we need to check the [Measure] and [Measure2]'s return is right also in visual without filter.

        The [Measure] is return the rankx for the "Sum of value"  in your side is the count and the rank type is "Skip".

        And you need to check the [Measure] is the use the COUNT() function in your side due to i use the SUM() function:

         

        RANKX( ALLSELECTED('Table'[Type]) , CALCULATE(COUNT('Table'[Session])  ,ALLEXCEPT('Table','Table'[Type])) ,,DESC,Skip)
         
        And then we need to check the [Measure2] can give the rankx we want in the end .[This is used to filter the visual to show the top5 in the end , we must check this measure return the right!]
         
        If above can not help you solve the issue ,can you share the sample pbix file to me ( you can upload your pbix file to OneDrive and share the link to here).Thanks in advance! 
         

        Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

         

        Best Regards,

        Aniya Zhang

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly