Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

TOPN Filtering by a Measure Workaround (Live semantic Model Connection)

Hello,

 

I am currently wokring on making a dashboard which should show Top 10 parts by a paremeter; only problem is this paremeter is  a measure so cannot be used for TOPN Filtering.

 

Solutions I have seen suggested creating new tables but I am using a dataset from a live connection to semantic model in SSAS so i am unable to modify the dataset in desktop.

 

Is there anyway to ovcercome this issue in this situation?

 

Thanks & Kind Regards, Adam P

Need Help
Tips and Tricks
  • rajendraongole1's avatar
    rajendraongole1
    2 years ago

    Hi Anonymous - To achieve the desired "Top 10 parts by a measure" visualization by using DAX.

     

    Create a measure to rank your parts

     

    RankMeasure =
    RANKX(
    ALL('PartsTable'),
    [YourMeasure],
    ,
    DESC,
    DENSE
    )

     

    you can replace PartsTable with the name of your table .

     

    In the "Filters on this visual" pane, drag RankMeasure and set it to <= 10.

    Did I answer your question? Mark my post as a solution! This will help others on the forum!
    Appreciate your Kudos!!

  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Anonymous ,

     

    Thanks for the reply from rajendraongole1  and Ashish_Mathur , please allow me to provide another insight: 

     

    You can try this measure.

    Top10Parts =
SUMX (
    TOPN (
        10,
        SUMMARIZE ( PartsTable, PartsTable[PartID], "MeasureValue", [YourMeasure] ),
        [YourMeasure], DESC
    ),
    [YourMeasure]
)

     

     For more details please refer to the document:

    TOPN function (DAX) - DAX | Microsoft Learn

    Measures in Analysis Services tabular models | Microsoft Learn

     

    If your Current Period does not refer to this, please clarify in a follow-up reply.

     

    Best Regards,

    Clara Gong

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

5 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Super User
    2 years ago

    Hi Anonymous - To achieve the desired "Top 10 parts by a measure" visualization by using DAX.

     

    Create a measure to rank your parts

     

    RankMeasure =
    RANKX(
    ALL('PartsTable'),
    [YourMeasure],
    ,
    DESC,
    DENSE
    )

     

    you can replace PartsTable with the name of your table .

     

    In the "Filters on this visual" pane, drag RankMeasure and set it to <= 10.

    Did I answer your question? Mark my post as a solution! This will help others on the forum!
    Appreciate your Kudos!!

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi thanks for this,

       

      Will this be dynamic and therefore show top 10 based on apllied filters or will it be a setlist?

       

      Thnaks, AP

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      HI,

       

      Thanks for your reply.

       

      The only problem with this is that this table will be filtered by different Business Units & therefore apllying the <=10 will not work.

       

      Would a seprate rank for each filter selection het around this?

       

      Kind Regards,

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    2 years ago

    Hi,

    Share some data to work with and show the expected result.   Share data in a format that can be pasted in an MS Excel file.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous ,

     

    Thanks for the reply from rajendraongole1  and Ashish_Mathur , please allow me to provide another insight: 

     

    You can try this measure.

    Top10Parts =
SUMX (
    TOPN (
        10,
        SUMMARIZE ( PartsTable, PartsTable[PartID], "MeasureValue", [YourMeasure] ),
        [YourMeasure], DESC
    ),
    [YourMeasure]
)

     

     For more details please refer to the document:

    TOPN function (DAX) - DAX | Microsoft Learn

    Measures in Analysis Services tabular models | Microsoft Learn

     

    If your Current Period does not refer to this, please clarify in a follow-up reply.

     

    Best Regards,

    Clara Gong

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