All Function Vs Values Function Usage in Topn

DAX DAX Calculations
1

Hello Teachers

May I ask something about the usage of All Function Vs Values Function in the Topn measures?

I am referencing from Mastering DAX Calculation - Advanced DAX Formula Combination Examples. I tried with All and Values in Topn and they are completely same results. May I know is if it is okay that we can use either of them?

Countrows with virual Table = 
COUNTROWS( CALCULATETABLE(Sales,
                              TOPN(5,VALUES(Products[Product Name]),CALCULATE([Total Sales],All('Date')),DESC)))

Countrows with virual Table (ALL) = 
COUNTROWS( CALCULATETABLE(Sales,
                              TOPN(5,All(Products),CALCULATE([Total Sales],All('Date')),DESC)))

Thank you.

Best Regards
Khin Kye Sin

2

@KhinKyeSin Looks like the evaluation context where you are calling this measure is not filtering Product Name. Can you please check and confirm?

ALL at the second argument of TOPN returns all the rows of the product table ignoring any existing filters over the products table.
ALL at the second argument of CALCULATE removes the filter from Date table.

VALUES returns all the rows that are currently visible in the current filter context, and in this case it looks like that the filter context for products name is empty, only in this particular scenario there is no difference between ALL and VALUES.

Always remember this difference, When ALL is used as a CALCULATE modifier, meaning it used on its own and is the top level function then it always removes the filters and doesn’t return any rows, but if used inside any iterator or outside CALCULATE then it returns all the rows of the table or the column specified in it.

1 Like
3

Thank you so much for the clear explanation.

While reading your explanation, I got it that All returns all the rows and removes the filters in the context.

However, Values will have effects from all the rows that are from filters only in the current context.

I am confusing myself by thinking about the Ranking lessons in the same Mastering DAX Calculation course. In the ranking lesson, there is a measure as below;

Top 3 Product Margin Sales =
CALCULATE( [Total Sales],
TOPN( 3, VALUES(Products[Product Name]),[Profit Margin],DESC))

Regarding to the measure above, if we have slicer on 2021 Year Filter, we will just look at the products sold in 2021.

In that case, we can still use the All Function which will consider all of the products in product table regardless of any year selected. But both will show the same results and no difference in this scenario. Right?

Thank you.

Best Regards
Khin Kye Sin

4

Without information of how you are slicing this measure I can’t confirm.

But if you were to put this measure in a matrix, with product name on rows, you will get same value as Total Sales for each product and not the information regarding top 3 products.

I would use something like this:

image
image955×610 23.3 KB

image
image952×634 23.3 KB

removing Total Sales:

image
image810×291 6.37 KB

image
image730×303 6.02 KB

If you have any questions on KEEPFILTERS, here is a post from @BrianJ Thinking Behind Use of KEEPFILTERS

1 Like
5

Hi @KhinKyeSin, we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.

  • Use the forum search to discover if your query has been asked before by another member.

  • When posting a topic with formula make sure that it is correctly formatted to preformatted text </>.

  • Use the proper category that best describes your topic

  • Provide as much context to a question as possible.

  • Include demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

I also suggest that you check the forum guideline https://forum.enterprisedna.co/t/how-to-use-the-enterprise-dna-support-forum/3951. Not adhering to it may sometimes cause delay in getting an answer.

6

Hi @KhinKyeSin , did the response provided by @AntrikshSharma help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!

7

Hi @KhinKyeSin, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Also, we’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!