Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

TopN in Visual Level Filter vs TopN DAX

What's the difference between using TopN in Visual Level filter and using the TOPN function of DAX?

What's the advantage of using one over the other?

 

Thanks!

Need Help
  • parry2k's avatar
    parry2k
    7 years ago

    Anonymous assume you have whatif parmeter called  Top X with values from 10 to 50 and in your TOPN you can use this whatif parmeter

     

    TOPN( [Top X], ..... )

     

    now when you use slider for Top X , you will get TOP records based on your parameter

     

     

8 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Anonymous -

    I added a single column to a table - Product Name, and did a top 20 by Sum of Sales Amount.

    Using DAX Studio, I scripted the DAX that was produced:

     

    DEFINE VAR __ApplyFilterSQDS0 = 
  TOPN(
    20,
    SUMMARIZECOLUMNS('Product'[Product Name], "SumSalesAmount", CALCULATE(SUM('Sales'[SalesAmount]))),
    [SumSalesAmount],
    0
  )

EVALUATE
  TOPN(
    501,
    CALCULATETABLE(
      DISTINCT('Product'[Product Name]),
      KEEPFILTERS(__ApplyFilterSQDS0)
    ),
    'Product'[Product Name],
    1
  )

ORDER BY
  'Product'[Product Name]

    So, they're essentially the same, because they both use TOPN. 

     

    Advantage of Visual filter: It's easy to use, especially for those who want to avoid DAX.

    Advantages of DAX calculation: It's more customizable, easier to debug and reusable.

    • parry2k's avatar
      parry2k
      Super User
      7 years ago

      Anonymous with dax you can use whatif parameter and pass it to TOPN dax to dynamically get top x values

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Thanks parry2k for the sharing! I see, this way I can "simulate" what would be my Top x values, for example, i have dynamic rates for the values.

         

        Question though, you can also pass measures in the TopN filter option right? (So I can also pass the whatif parameters there). How would this be different?

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi Anonymous,

       

      Thanks for the response! Could you provide a more concrete example on how DAX will be more customizable and reusable?

      • parry2k's avatar
        parry2k
        Super User
        7 years ago

        Anonymous assume you have whatif parmeter called  Top X with values from 10 to 50 and in your TOPN you can use this whatif parmeter

         

        TOPN( [Top X], ..... )

         

        now when you use slider for Top X , you will get TOP records based on your parameter