Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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 -
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.
Hi @Anonymous,
Thanks for the response! Could you provide a more concrete example on how DAX will be more customizable and reusable?
@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
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@Anonymous with dax you can use whatif parameter and pass it to TOPN dax to dynamically get top x values
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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 - 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. 🙂
@Anonymous no, you cannot pass parameter to filter.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
100 | |
78 | |
76 | |
52 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |