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

[Anonymous]

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

[rajendraongole1 · Accepted solution]

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 · Accepted solution]

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.

[Ashish_Mathur]

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.

[rajendraongole1 · Accepted solution]

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]

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,

[Anonymous]

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