What are the differences among the measures?

[Anonymous]

Hi,

I have a very simple test table as below.

col1	col2	col3	col4
A	1	t	1
B	2	t	2
C	3	k	1

and three measures:

s1 := CALCULATE( SUM(Table1[col2]),
		ALLEXCEPT( Table1,Table1[col3]),
		FILTER( Table1, 
			Table1[col4] = 1))

s2 := CALCULATE( SUM(Table1[col2]),
		FILTER( ALLEXCEPT( Table1, Table1[col3]),
			Table1[col4] = 1))

s5 := CALCULATE( SUM(Table1[col2]),
		ALLEXCEPT( Table1, Table1[col3]),
		Table1[col4] = 1)

I added [col1] and  [col4] as the slicers and found:

If I selected B for the slicer [col1], s1 was blank and the other two got 1. Similar result would be got when I selected 2 for the slicer [col4].

RESULT when selecting B for slicer [col1]

I wanted to understand what the differences are among these measures. Thank you in advanced.

[d_gosbell · Accepted solution]

So when you explicitly filter for [Col2] = "B"

You are also generating crossfilters for the other columns on the second row of your table, so there are implied filters for:

[Col3] = "t"

[Col4] = 2

Given this the expression ALLEXCEPT( Table1,Table1[col3]) can be thought of as - Give me all rows where Col3 = "t"

For S1

• ALLEXCEPT and FILTER are evaluated separately
• the ALLEXCEPT would return rows 1 & 2
• the Table1 reference inside the FILTER is evaluated in the context of the slicer, so it can only see row 2, so the condition [Col4] = 1 is false so no rows are returned.

For S3

• The ALLEXCEPT returns the first 2 rows
• The FILTER then filters these 2 rows for [Col4] = 1 which returns the first row

For S5

• The ALLEXCEPT and filter condition are evaluated separately as in S1
• the ALLEXCEPT would return rows 1 & 2
• simple conditions in a CALCULATE like [Col4]=1 are evaluated as FILTER( ALL( [Col4] ) , [Col4] = 1) which would return rows 1 & 3
• the results of the two filters are intersected and the result is the first row (as this is the only row in common to both of the calculate filter arguments)

Note: technically the engine does not process the results in terms of "rows", it deals with filter contexts on the various columns. It's just easier to explain this in terms of the rows of data in your example.

[Anonymous]

Hi d_gosbell, thanks for your prompt reply. It is very clear and easy to understand. Thank you !