Forum Discussion

rynhowl's avatar
rynhowl
Microsoft Employee
5 years ago
Solved

Filter TOPN or TOPN

I want to filter to the Top 5 items by number or users OR Top 1 item by latest version.

 

App Name

Users

AppVersion1

  5
AppVersion2  15
AppVersion3  45
AppVersion4  2000
AppVersion5  125
AppVersion6  3000
AppVersion7  4500
AppVersion8  250000
AppVersion9  500000
AppVersion10  150

 

In this case, I want the visual to include AppVersion4, AppVersion6, AppVersion7, AppVersion8 and AppVersion9 because they are the top 5 by users, but I also want to include AppVersion10 because it is the latest version.

 

Any ideas?

Need Help
  • v-janeyg-msft's avatar
    v-janeyg-msft
    5 years ago

    Hi, rynhowl 

     

    According to your description, I think you need to create a measure to filter data in filter pane.

    Like this:

     

    Measure2 =
VAR rank1 =
    RANKX ( ALL ( Table1 ), CALCULATE ( MAX ( Table1[Users] ) ),,, DENSE )
VAR a =
    MAXX (
        SUMMARIZE (
            ALL ( Table1 ),
            [App Name],
            [Users],
            "rank2", VALUE ( SUBSTITUTE ( Table1[App Name], "AppVersion", "" ) )
        ),
        [rank2]
    )
VAR b =
    VALUE ( SUBSTITUTE ( SELECTEDVALUE ( Table1[App Name] ), "AppVersion", "" ) )
RETURN
    IF ( rank1 <= 5 || b = a, 1, 0 )

     

     

    If it doesn’t solve your problem, please feel free to ask me.

     

    Best Regards

    Janey Guo

     

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

  • Anand24's avatar
    Anand24
    5 years ago

    Hi rynhowl ,

    Create a measure with below dax and filter the visual/page according to it.

    Top Rank Filter =
    Var a = RANKX ( ALL ( 'Table' ), CALCULATE ( MAX ( 'Table'[Users] ) ),,, DENSE )
    Var b = RANKX ( ALL ( 'Table' ), CALCULATE ( VALUE(MID(MAX('Table'[App Name]),11,4)) ),,, DENSE )

    Return
    IF(a <= 5 || b = 1, "Show","Don't Show")


    PFA attached pbix for your reference.

     

     

     

    Give a Thumbs Up if this post helped you in any way and Mark This Post as Solution if it solved your query !!!

    Proud To Be a Super User !!!
    LinkedIn

3 Replies

  • v-janeyg-msft's avatar
    v-janeyg-msft
    Community Support
    5 years ago

    Hi, rynhowl 

     

    According to your description, I think you need to create a measure to filter data in filter pane.

    Like this:

     

    Measure2 =
VAR rank1 =
    RANKX ( ALL ( Table1 ), CALCULATE ( MAX ( Table1[Users] ) ),,, DENSE )
VAR a =
    MAXX (
        SUMMARIZE (
            ALL ( Table1 ),
            [App Name],
            [Users],
            "rank2", VALUE ( SUBSTITUTE ( Table1[App Name], "AppVersion", "" ) )
        ),
        [rank2]
    )
VAR b =
    VALUE ( SUBSTITUTE ( SELECTEDVALUE ( Table1[App Name] ), "AppVersion", "" ) )
RETURN
    IF ( rank1 <= 5 || b = a, 1, 0 )

     

     

    If it doesn’t solve your problem, please feel free to ask me.

     

    Best Regards

    Janey Guo

     

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

  • lbendlin's avatar
    lbendlin
    Super User
    5 years ago

    Add a measure flag that ORs the two conditions. Then filter the visual by the measure. Where are you stuck?

  • Anand24's avatar
    Anand24
    Super User
    5 years ago

    Hi rynhowl ,

    Create a measure with below dax and filter the visual/page according to it.

    Top Rank Filter =
    Var a = RANKX ( ALL ( 'Table' ), CALCULATE ( MAX ( 'Table'[Users] ) ),,, DENSE )
    Var b = RANKX ( ALL ( 'Table' ), CALCULATE ( VALUE(MID(MAX('Table'[App Name]),11,4)) ),,, DENSE )

    Return
    IF(a <= 5 || b = 1, "Show","Don't Show")


    PFA attached pbix for your reference.

     

     

     

    Give a Thumbs Up if this post helped you in any way and Mark This Post as Solution if it solved your query !!!

    Proud To Be a Super User !!!
    LinkedIn