CALCULATE and FILTER combination required for measure; why?

[Anonymous]

I'm creating some simple measures and ran into a weird problem (at least for me). I got everything working, but I don't understand why. I hope one of you can help me understand why and maybe teach me about a different approach that might be better. 

Measure 1 looks something like this. It's a sort a more broad measure;

Measure 1 = 
CALCULATE (
     SUM (Table_A[Column_B]),
     Table_X[Column_Y] <> 'Value 1'
)

Measure 2 looks like this. It's supposed to be a subset of the value of Measure 1.

Measure 2 = 
CALCULATE (
     [Measure 1],
     Table_X[Column_Y] = 'Value 3'
)

Let's assume 'Table_X[Column_Y]' has the distinct values 'Value 1', 'Value 2', 'Value 3'.

I would expect this to work, but it doesn't.  Measure 2 seems to ignore the filter "Table_X[Column_Y] = 'Value 3'"

If I write the measure like this, it does work:

Measure 2 = 
CALCULATE (
     [Measure 1],

     FILTER (
          Table_X,
          Table_X[Column_Y] = 'Value 3'
     )
)

As far as I know, the CALCULATE function is basically just an abstraction of the FILTER function in combination with the ALL function. Now I'm not so sure anymore.

I get that it has to do with the fact I'm filtering on the same column. In my mind though, [Measure 1] should return everything with 'Value 2' and 'Value 3'. [Measure 2] should just further refine that to only include 'Value 3'.

I hope this makes sense, and somebody is able to help me understand PowerBI and DAX a little better. 

Thanks,

Wilco

[Jos_Woolley · Accepted solution]

Thanks. With nested CALCULATE functions, the innermost filter overrides the next innermost, etc., etc.

See here for an explanation:

https://dax.tips/2020/03/23/dax-keepfilters/ 

Regards

[AlexisOlson · Accepted solution]

This is a case where filtering on entire tables rather than columns can cause confusion.

For example, these two measures are not equivalent.

M1 =
CALCULATE (
    [Measure 1],
    FILTER ( VALUES ( Table_X[Column_Y] ), Table_X[Column_Y] = "Value 3" )
)

M2 =
CALCULATE (
    [Measure 1],
    FILTER ( Table_X, Table_X[Column_Y] = "Value 3" )
)

In [M1], the filter on Table_X[Column_Y] is overridden by the filtering inside [Measure 1]. Because of this, changing VALUES to ALL would not make any difference and this measure returns the same results as [Measure 1].

In [M2], the filter on Table_X[Column_Y] is overridden but there are still may be filters on other columns of Table_X that don't get overridden.

Suppose Table_X filters Table_A via a one-to-many relationship on [ID] where they look like this.

Table_X:

Table_A:

The expression FILTER ( Table_X, Table_X[Column_Y] = "Value 3" ) filters the table to the rows with ID = 4 and 5. Even though [Measure 1] replaces filters on [Column_Y], the ID filtering is still in place.

Thus [M2] returns the sum of [Column_B] where Table_X[Column_Y] <> "Value 1" (ID = 3,4,5) is the inner filter and Table_X[ID] IN {4, 5} is the remaining outer filter, so the only row of Column_B remaining is where [ID] = 4, [Column_B] = 9.

As a final note, using FILTER is not strictly required. These KEEPFILTERS versions will combine filters more like you were expecting:

Measure 1 =
CALCULATE (
    SUM ( Table_A[Column_B] ),
    KEEPFILTERS ( Table_X[Column_Y] <> "Value 1" )
)

Measure 2 =
CALCULATE (
    [Measure 1],
    KEEPFILTERS ( Table_X[Column_Y] = "Value 3" )
)

Recommeded further reading:
https://www.sqlbi.com/articles/using-keepfilters-in-dax/

https://www.sqlbi.com/articles/order-of-evaluation-in-calculate-parameters/

[Anonymous]

Thanks for this, @AlexisOlson! 

I have to say, it still boggles me a little. Especially this part

'Even though [Measure 1] replaces filters on [Column_Y], the ID filtering is still in place.' 

I understand what you're saying, and that makes sense. But I don't fully get why it filters on ID when I only filter on Table_X[Column_Y]. My interpretation would be that is because when I SUM Table_A[Column_B], the relationship is on the iD column, and therefore, PowerBI 'stores' the filter as 'Table_X[ID] IN {4, 5}, as you say. This means we don't really filter on Column_Y, but on ID instead. And therefore, it doesn't get overridden Is that correct?

If that's true, I don't get why the article @Jos_Woolley linked suggested using VALUES in this case. So I really need to re-re-read that article as well, as well as the links you've shared. 

And I think I indeed get confused with the table vs column filtering. I didn't realize CALCULATE is allways a table-wide filter (correct, right?). Knowing that this stuff makes more sense. 

Thank you (@AlexisOlson , @Jos_Woolley) so much for trying to explain these basics to me. Your time is greatly appreciated. I have some further reading to do. Now I just need to figure out which comment to accept as solution. Haha. 

[AlexisOlson]

---

@Anonymous wrote:

I understand what you're saying, and that makes sense. But I don't fully get why it filters on ID when I only filter on Table_X[Column_Y]. My interpretation would be that is because when I SUM Table_A[Column_B], the relationship is on the iD column, and therefore, PowerBI 'stores' the filter as 'Table_X[ID] IN {4, 5}, as you say. This means we don't really filter on Column_Y, but on ID instead. And therefore, it doesn't get overridden Is that correct?

When you write FILTER ( Table_X, ... ), the entire table is used for filtering even if the filter condition in the second argument only refers to one column. This is why ID is part of the filter context, not because the relationship is on ID. It's filtering based on all table columns (including any additional columns if they're present) but [Column_Y] is the only column that's affected by (gets overridden by) [Measure1].

If that's true, I don't get why the article @Jos_Woolley linked suggested using VALUES in this case. So I really need to re-re-read that article as well, as well as the links you've shared. 

Using VALUES is useful to do filtering on a single column (rather than a table) while keeping the existing filter context on that column (using ALL removes filter context). My [M1] measure is a perfectly fine and useful measure (and essentially equivalent to my KEEPFILTERS version of [Measure 2]) since it avoids the entire table filter.

And I think I indeed get confused with the table vs column filtering. I didn't realize CALCULATE is allways a table-wide filter (correct, right?). Knowing that this stuff makes more sense. 

You can have column filters or table filters in CALCULATE. "There are few golden rules in the DAX world, but one for sure is: never filter a table when you can filter a column."

Thank you (@AlexisOlson , @Jos_Woolley) so much for trying to explain these basics to me. Your time is greatly appreciated. I have some further reading to do. Now I just need to figure out which comment to accept as solution. Haha. 

---

You can accept multiple solutions if you'd like.

[Anonymous]

The more you know, the less you know, right? So much stuff to learn and understand! Thanks for all the input. I'll make sure to go through everything again to fully comprehend it. It's clear I'm still not understanding everything. 

[DimaMD]

@Anonymous Can you provide a file with pbix data?

[Anonymous]

Thanks for responding, @DimaMD, and apologies for my late reply. 

Unfortunately, I don't have an (anonymized) pbix file available at the moment. I've already gotten loads of information in this thread, though. 

Thank anyway!

[Jos_Woolley · Accepted solution]

Thanks. With nested CALCULATE functions, the innermost filter overrides the next innermost, etc., etc.

See here for an explanation:

https://dax.tips/2020/03/23/dax-keepfilters/ 

Regards

[Anonymous]

Thanks a bunch, Jos! Also for linking that article. I didn't find it myself. 

1 thing I'm still missing though; please bear with me. In my example, the filter applied in 'Measure 2' is ignored for the filter used in [Measure 1] (meaning the result in the 'faulty' [Measure 2] is the same as the result of [Measure 1].

If I understand the article correctly, the innermost filter overrides the next innermost, just like you said as well. But why does the 'new' [Measure 2] work then? Wouldn't it still be overridden by the filter in [Measure 1] since that's the most innermost filter?

What am I missing? 

Thanks again!

[Jos_Woolley]

You perhaps need to re-read the article with respect to the comments regarding the ALL function.

Your first attempt:

Measure2: =
CALCULATE ( [Measure 1], Table_X[Column_Y] = "Value 3" )

is equivalent to:

Measure2: =
CALCULATE (
    [Measure 1],
    FILTER ( ALL ( Table_X[Column_Y] ), Table_X[Column_Y] = "Value 3" )
)

and this ALL makes a visually small but very important difference over your final attempt:

Measure2: =
CALCULATE ( [Measure 1], FILTER ( Table_X, Table_X[Column_Y] = 'Value 3' ) )

Regards

[Anonymous]

Hi Jos,

Thanks! I get what you're saying. And I understand the difference the ALL is making. What I don't get, is the order in which it is applied. 

If the most inner filter (in [Measure 1], right?) overrides the next one (The one in [Measure 2], why does the 'ALL' in [Measure 2] matter if it'll get overridden by the filter in [Measure 1] any way? It suggests the order is the other way around, but I don't get why the filter in [Measure 2] would be the most 'inner' function. 

Additionally, I tried the suggestion in the article, adding the 'VALUE' function to [Measure 2]. That didn't give the right output either. 

Am I correct in thinking the 'working' measure 2 can be written as follows:

Measure 2 = 
CALCULATE (
     CALCULATE (
          SUM ( Table_A[Column_B] ),
          FILTER ( ALL ( Table_X[CalumnY] ), Table_X[Column_Y] <> 'Value 1' )
     ),
     FILTER ( Table_X[Column_Y], Table_X[Column_Y] = 'Value 3' )
)

If that's the case, the most inner part (which should override the outer part) should override the part without the 'ALL', right? 

I'm sure I'm missing something simple, but I can't seem to wrap my head around it. I hope you'll have the patients to help me understand. Your input so far is greatly appreciated!

Cheers,

Wilco

[AlexisOlson]

It's not the ALL that matters for the two version in your original post, it's the fact that it's a table filter rather than a column filter, which means other columns may be involved.

[Jos_Woolley]

Hi,

What's the relationship betwen Table_A and Table_X?

Regards

[Anonymous]

Thanks for replying, Jos!

There's Many (Table_A) to 1 (Table_X) relationship between them.