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,

 

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.