DAX Help

[Anonymous]

What if we need to display Top N/Bottom N products and regions both.

It should show only top 5 or bottom 5 products and regions. Only 5 row table.

Following measure works for only products.

dynamic time period with top rank =

Var N = SELECTEDVALUE('Top N Value'[Top N Value])

VAR RankType = SELECTEDVALUE('TopBottom Selector'[RankType])

VAR IsTop = RankType = "Top"

VAR IsBottom = RankType = "Bottom"

VAR BaseMeasure = [Dyanamic Time Period Measure]

// Create a table of products with non-blank measure values

VAR ProductTable =

    FILTER(

        ALLSELECTED(Products[ProductName]),

        NOT ISBLANK(CALCULATE([Dyanamic Time Period Measure]))

    )

// Rank products based on the selected measure

VAR ProductRankTop =

    RANKX(

        ProductTable,

        CALCULATE([Dyanamic Time Period Measure]),

        ,

        DESC

    )

VAR ProductRankBottom =

    RANKX(

        ProductTable,

        CALCULATE([Dyanamic Time Period Measure]),

        ,

        ASC

    )

RETURN

    SWITCH(

        TRUE(),

        IsTop && ProductRankTop <= N, BaseMeasure,

        IsBottom && ProductRankBottom <= N, BaseMeasure,

        BLANK()

    )

Any change needs to be done in this measure also?

Selected Measure top N rank =

SWITCH(SELECTEDVALUE('Measure Selector'[Measure Name]),"Sales",[Total Sales],"Quantity",[Total Quantity],"GP",[Total GP])

Like this? 

[v-kpoloju-msft]

Hi @Anonymous,

Thank you for reaching out to the Microsoft fabric community forum. Also thanks to @bhanu_gautam, for his inputs on this thread. I reproduced the scenario, and it worked on my end. I used my sample data and successfully implemented it.

Outcome:

I am also including .pbix file for your better understanding, please have a look into it:

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you for using Microsoft Community Forum.

[Anonymous]

Awesome solution, 

But state name and Product name are in different table. Which has no relationship between them.

What can we do about that?

What needs to be changed?

Please show us required code according to this.

[v-kpoloju-msft · Accepted solution]

Hi @Anonymous,

Thank you for reaching out to the Microsoft fabric community forum.  I reproduced the scenario again, and it worked on my end. I used my sample data and successfully implemented it.

See there is No relationship between Products and Regions:

Outcome:

I am also including .pbix file for your better understanding, please have a look into it:

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you for using Microsoft Community Forum.

[bhanu_gautam]

@Anonymous To display the top 5 or bottom 5 products and regions, you need to modify your measure to account for both products and regions.

dynamic time period with top rank =
VAR N = SELECTEDVALUE('Top N Value'[Top N Value])
VAR RankType = SELECTEDVALUE('TopBottom Selector'[RankType])
VAR IsTop = RankType = "Top"
VAR IsBottom = RankType = "Bottom"
VAR BaseMeasure = [Dyanamic Time Period Measure]

// Create a table of products and regions with non-blank measure values
VAR CombinedTable =
FILTER(
CROSSJOIN(ALLSELECTED(Products[ProductName]), ALLSELECTED(Regions[RegionName])),
NOT ISBLANK(CALCULATE([Dyanamic Time Period Measure]))
)

// Rank combined products and regions based on the selected measure
VAR CombinedRankTop =
RANKX(
CombinedTable,
CALCULATE([Dyanamic Time Period Measure]),
,
DESC
)

VAR CombinedRankBottom =
RANKX(
CombinedTable,
CALCULATE([Dyanamic Time Period Measure]),
,
ASC
)

RETURN
SWITCH(
TRUE(),
IsTop && CombinedRankTop <= N, BaseMeasure,
IsBottom && CombinedRankBottom <= N, BaseMeasure,
BLANK()
)

You may need to ensure that your Selected Measure top N rank measure is correctly integrated with this new logic.

Selected Measure top N rank =
SWITCH(
SELECTEDVALUE('Measure Selector'[Measure Name]),
"Sales", [dynamic time period with top rank],
"Quantity", [dynamic time period with top rank],
"GP", [dynamic time period with top rank]
)

[Anonymous]

This is complete different measure

Selected Measure top N rank =

SWITCH(

    SELECTEDVALUE('Measure Selector'[Measure Name]),

    "Sales", [Total Sales],

    "Quantity", [Total Quantity],

    "GP", [Total GP]

)

Please fix code as its not working