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

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
Icon for Microsoft Employee rankMicrosoft 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
    8 years ago

    Hi v-jiascu-msft

     

    The ALLExcept function did not work for me.

    The columns: Product name and Sales quantity are coming from the same table. And the column that I'am trying to get in the table is from another table. But the problem seems to occur even if I bring the any other column from the same table.

    What could be the possible reason, any idea?

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

      Hi Anonymous,

       

      Can you share your file and the result you want? Please mask the private parts first. A dummy one will be great.

       

      Best Regards,

      Dale

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        Hey v-jiascu-msft

         

        I tried the method where we create a "what if" parameter for top N filter. I'am able to solve the problem partially. I will get back to you if I need help. Thanks for all the help.