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