Context in Iterator functions: dummy example

[Anonymous]

Hello Community,

I am afraid I do not understand how context is computed in Iterator Functions. I have been doing some tests and I really do not understand the results. Can somebody give some light into it?

Assume the following dummy data (where P/A is the ratio between Performance and Amount).

I define:

measureA :=
DIVIDE (
    SUM ( 'Table'[Performance] ),
    CALCULATE ( SUM ( 'Table'[Amount($)] ), ALL ( 'Table'[Store] ) )
)

Which intends to measure how your store performs compared to the others. The measure of course depends on the context (and it only "makes sense" if you include the Store in your chart).

Let's create:

test1 := MINX ( 'Table', [measureA] )
test2 := MINX ( ALL('Table'), [measureA] ) 
test3 := MINX ( ALL('Table'[Store]), [measureA] )
test4 := MINX ( ALL('Table'[Country]), [measureA] )
test5 := MINX ( ALL('Table'[Year], 'Table'[Country], 'Table'[Store]), [measureA] )

Which yields:

Test1 & Test2:

In iterative functions, Power BI computes the expression (in this case, measureA) for each row of the table, according to the documentation. By "table" we mean the first argument of the function I guess.

But I do not understand in what context is expression being computed.

Seeing the results, my guess is that measureA is computed without a filter context, only row context, so even if the denominator should sum across all stores, it does not.  Am I correct?

That's why:

1. For Test1, the result is exactly the same as column P/A. 

2. For Test2, indeed Power BI is first computing column P/A and then taking the minimum of all of them. 

Test3 

1. Why does this even work? In Test3 for example, ALL(Table[Store]) returns a table with one column, the Store. However, [measureA] is computed using columns Amount and Performance, which do not even exist in the table returned by ALL(Table[Store]). Shouldn't this throw an error?

2. I understand in Test3, measureA is now NOT being computed in a ROW context but in a FILTER context where Store is being ignored. Now, the CALCULATE in measureA can sum across all stores for the same year and country, as it is supposed to do. Indeed, Power BI is computing the following yellow column 

and then takes the minimum of all of them also in a context where it ignores the Store which yields to the final result.

Test4

Judging by the results, it is also computing the intermediate yellow column of above and then taking the minimum in a context where Country is ignored.

But for the yellow column to be computed, you need the denominator of measureA to sum across all Stores, which PowerBI should not be doing, just as in Test1 that it ignores it. I just told in Test4 to ignore the filters in the Country (which is not even being used by measureA).

So basically, I am really not sure which criteria is Power BI applying to sometimes compute the

CALCULATE ( SUM ( 'Table'[Amount($)] ), ALL ( 'Table'[Store] ) ) of measureA as expected, or ignoring it and just taking the value of the current row.

I know it is a long post, I appreciate all your responses in advance.

PS: find attached both the Excel and PBI file I have created.

Best regards,

[v-kkf-msft · Accepted solution]

Hi @Anonymous ,

Please read this article to understand the context of the iterator function.

The following takes the measure "test4" as an example to explain its context.

The MINX function returns the smallest value that results from evaluating an expression for each row of a table. So we can concatenate the result of an expression evaluated for each row in a table, using the following measure.

ConcatenateX = 
	CONCATENATEX( 
		ALL('Table'[Country]), 
		[measureA], 
		"," 
	)

ALL('Table'[Country]) returns 3 values. According to the documentation, we know that it will perform 3 iterations.

• For the first table in the figure, measureA needs to be calculated iteratively for three countries based on the filter Year and the Store. The result of the iteration is the combination of the three columns of the matrix.

• For the second table in the figure, measureA needs to be calculated iteratively for three countries based on the filter Year. The result of the iteration is the combination of the three columns of the matrix.

Figure:

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.