Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Top N as a dynamic filter

Hello all,   I want to create a slicer for top N products by sales. I need a filter which has values: 1) top1-10 2) top 10-20 3) top 20-30   Whenever the user clicks on any of the three the pr...
Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    8 years ago

    Hi Anonymous,

     

    1. Create a table like below. Do not establish relationship with other tables.

    Rule                                                                                                                                 ID

    top 1 - 10 1
    top 10 - 20 2
    top 20 - 30 3

     

    2. Create a measure like below.

    rankColorName =
VAR rankShouldBe =
    RANKX (
        ALL ( DimProduct[ProductName] ),
        CALCULATE ( SUM ( FactSales[SalesQuantity] ) )
    )
RETURN
    IF (
        HASONEFILTER ( ruleTable[Rule] ),
        IF (
            MIN ( ruleTable[ID] ) = 1
                && rankShouldBe <= 10,
            rankShouldBe,
            IF (
                MIN ( ruleTable[ID] ) = 2
                    && rankShouldBe > 10
                    && rankShouldBe <= 20,
                rankShouldBe,
                IF (
                    MIN ( ruleTable[ID] ) = 3
                        && rankShouldBe > 20
                        && rankShouldBe <= 30,
                    rankShouldBe,
                    BLANK ()
                )
            )
        ),
        rankShouldBe
    )

    3. Create a table visual and a slicer like below.

    Top_N_as_a_dynamic_filter

     

    Best Regards,

    Dale

v-jiascu-msft's avatar
v-jiascu-msft
Microsoft Employee
8 years ago

Hi Anonymous,

 

1. Create a table like below. Do not establish relationship with other tables.

Rule                                                                                                                                 ID

top 1 - 10 1
top 10 - 20 2
top 20 - 30 3

 

2. Create a measure like below.

rankColorName =
VAR rankShouldBe =
    RANKX (
        ALL ( DimProduct[ProductName] ),
        CALCULATE ( SUM ( FactSales[SalesQuantity] ) )
    )
RETURN
    IF (
        HASONEFILTER ( ruleTable[Rule] ),
        IF (
            MIN ( ruleTable[ID] ) = 1
                && rankShouldBe <= 10,
            rankShouldBe,
            IF (
                MIN ( ruleTable[ID] ) = 2
                    && rankShouldBe > 10
                    && rankShouldBe <= 20,
                rankShouldBe,
                IF (
                    MIN ( ruleTable[ID] ) = 3
                        && rankShouldBe > 20
                        && rankShouldBe <= 30,
                    rankShouldBe,
                    BLANK ()
                )
            )
        ),
        rankShouldBe
    )

3. Create a table visual and a slicer like below.

Top_N_as_a_dynamic_filter

 

Best Regards,

Dale

Anonymous's avatar
Anonymous
Not applicable
6 years ago

I have a similar situation. i tried this method. It does work. However, i cant get it to closure since i dont want to show the  "rankcolorName" in the view. 

 

How can i achieve it?