calculatetable query

[CloudMonkey]

Hi,

I'm having trouble understanding the difference in behaviour between these two table formulae:

Test 1 = CALCULATETABLE (
            CALCULATETABLE(
                 'Sales Territory',
                 ALL('Sales Territory'[Country])
            ),
            'Sales Territory'[Country] = "United States"
)

Test 2 = CALCULATETABLE (
            CALCULATETABLE(
                 'Sales Territory',
                 DISTINCT('Sales Territory'[Country])
            ),
            'Sales Territory'[Country] = "United States"
)

I'm finding the first formula returns all rows (regardless of what the country is), but the 2nd formula only returns rows where the country is "United States". Please can you tell me why the first formula doesn't filter by "United States"?

Thanks,

CM

[Cmcmahan · Accepted solution]

In addition to the info @Anonymous supplied, you should also know that when you nest CALCULATEs or CALCULATETABLEs, filters are evaluated from outside -> in. 

So in the first one, you filter where [Country] = US, and then the inner calculate's filter removes any pre-applied filters on [Country] with ALL([Country]).  Including your [Country] = US filter from the outer calculate!  

In your second example, the outer filter gets items where [Country] = US, and then the inner gives a DISTINCT list of those values.  You don't use ALL, so it doesn't remove any filters, it just stacks more on.  Since you're only expecting one value of [Country] from your first filter, it doesn't seem like the DISTINCT actually does anything.

[CloudMonkey]

Thanks both, I'm used to using CALCULATE/FILTER combinations so am used to filters flowing right to left in the equation so this left to right flow caught me out. Thanks

[Anonymous]

DAX Guide (https://dax.guide) is a great resource for understanding the nitty gritty of formulas.

ALL:

Returns all the rows in a table, or all the values in a column, ignoring any filters that might have been applied.

Remarks

This function removes the corresponding filters from the filter context. It does not materialize the resulting table when called directly in a filter argument of CALCULATE or CALCULATETABLE .

ALL can be used as a table expression when it has at least one argument.
ALL without arguments can be used only as a CALCULATE or CALCULATETABLE modifier and removes all the filters from the filter context.

Example

The ALL function can be applied to either a table or a set of columns.

ALL ( Customer )
 
ALL ( Customer[Country], Customer[State] , Customer[City] )

DISTINCT:

Returns a one column table that contains the distinct (unique) values in a column, for a column argument. Or multiple columns with distinct (unique) combination of values, for a table expression argument.

Remarks

If the parameter is a single table, the result of DISTINCT is affected by the current filter context. In this case it is important to understand the differences with VALUES, which might add an additional blank row in certain conditions.
If the parameter is a table expression, DISTINCT returns a table by removing duplicate rows provided by the table expression.

Hope this helps!