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 products get filtered by sales values and are displayed.

Can this be done? If not, anything similar to this is also most welcome.

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

8 Replies

  • 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
      8 years ago

      Hi v-jiascu-msft,

       

      I'am facing an issue

      The moment I bring in a new column in the table, the filter does not fuction and it shows all products.

      What could be the reason?

       

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

        Hi Anonymous,

         

        Where is the column from? Maybe you can make some changes like below.

         

        rankColorName =
VAR rankShouldBe =
    RANKX (
        ALL ( DimProduct[ProductName] ),
        CALCULATE ( SUM ( FactSales[SalesQuantity] ), ALLEXCEPT(DimProduct, DimProduct[Product Name] ) )
    )
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
    )

         

        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?

  • Pulkit's avatar
    Pulkit
    Resolver I
    8 years ago

    You can use RANKX and bookmarks to achieve this. 
    Create the visual, then in the visual level filter do the basic filtering to select only the ranks 1-10 (I am assuming you will give 1st rank to the highest sales), then bookmark it. Do the same for TOP N = 11-20 and 21-30.

    After creating the three bookmarks add them to buttons. This is not filter but it will do the the job.