Simple CALCULATE measure is testing my fundamental DAX understanding

[MarianoG]

Hi All,

This simple CALCULATE measure is testing my fundamental DAX understanding and I would like to draw from your wisdom.

The following screenshot should explain the situation, but I will explain further below:

I have this table:

Code	Category	Period
1	A	P1
2	A	P2
3	B	P2
4	C	P1

I created a measure: "Count Of P2" to count the number of rows with Period = P2 as follows:

Count of P2 =

CALCULATE(COUNTROWS('Table'),'Table'[Period] = "P2")

which is equivalent to:

Count of P2 =

CALCULATE( COUNTROWS('Table'),

FILTER( ALL('Table'[Period]), 'Table'[Period] = "P2"))

When I apply a filter on Category = A or B and Period = P1, I would expect the measure to remove the filter on Period and replace it with Period = P2, resulting in an overall combination of filters as Category = A or B and Period = P2, which should result in only two rows being counted by Countrows, but the result I get is only 1 row counted.

I hope someone with more experience can help me understand where I am getting it wrong.

Much appreciated,

Mariano

[MarianoG]

Thanks Owen!

Great explanation.

[OwenAuger · Accepted solution]

Hi there @MarianoG 

This admittedly unintuitive behaviour has do with auto-exist, in particular how it behaves when multiple filters are applied on the same table.

This article explains what's going on better than I could, with a similar example to yours:

https://www.sqlbi.com/articles/understanding-dax-auto-exist/

This behaviour can rear its head when filters are applied on columns of the same table, and you remove one of the filters via DAX with ALL or REMOVEFILTERS. The DAX query that is generated by the visual uses SUMMARIZECOLUMNS, which combines filters on the same table into a single filter, which effectively includes only existing combinations of the columns filtered. Removing the filter on one of the columns via DAX (in this case the Period column) does not remove the effect of the other column (Category) having been limited as a result of the original Period filter.

What can you do about it?

One solution is to move Category and Period to related dimension tables (i.e. create a star schema), and filter on columns in those tables instead.

Regards,

Owen