TopN in Visual Level Filter vs TopN DAX

[Anonymous]

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!

[v-juanli-msft]

Hi girinpanda 

It seems natelpeterson  and parry2k have give useful tips.

Could you kindly accept their answers as solutions so others may find this post quicklly?

In addition, here is an article for references

Dax power bi dynamic topn made easy with what if parameter

Best Regards
Maggie

[Anonymous]

@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.

[Anonymous]

Hi @Anonymous,

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

[parry2k · Accepted solution]

@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

[parry2k]

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

[Anonymous]

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]

@Anonymous - The measure defines the TopN. For instance top 10 Products average sales. 

TopN = <Hard-coded Number>

Attribute = Product

Fact = Sales

Measurement = Average

@parry2k is talking about a way to modify the TopN through interactions. You could even select each of the Attribute, Fact, and Measurement with parameters and update accordingly. Whatever you want to do. 🙂

[parry2k]

@Anonymous no, you cannot pass parameter to filter.