ALL() not working as expected when putting columns of one Dimension table into matrix

[Mikelytics]

Dear community, 

I work with Power BI for a very long time now, I frequently solve requests in the community and I was pretty sure that I have a very very good understanding of how CALCULATE() with ALL() and the other related functions work since I use it all the time without big problems. But recently in a session I built very very easy scenario to show somebody how filter context and CALCLATE() work in combination with ALL(). ANd there I was a little bit confused about the behaviour of Power BI in a very specific case which was when I put ALL() on a column in a dimension table, replace it with another value and then use the measure in combination with the ALL-column itself as well as a another column from the same dimension table within a matrix.

Maybe it is some kind of brain fog I have 😄 or an outlier or there is really a very basic behaviour which I did not know about before. So I hope you can help out here to explain what happens in the background. 🙂

Lets go to the sceanrio.

This is my very easy data model:

with the following customer table and product table (focus is on customer table):

the fact table has 100 transactions and its a tpyical 1:n relation.

Now I write two measures:

Sales Amount = SUM(Fact_Sales[Values])

Sales Amount Group A with ALL on Group Column= 

CALCULATE(
    [Sales Amount],
    FILTER(
        ALL(Dim_Customer[CategoryGroup]),
        Dim_Customer[CategoryGroup] = "CGroup A"
    )
)

Question: In the picture below I can explain every single value except the combination highlighted in red. Because since I put ALL() on the group and replace it with the Value Group A I would expect to see in the field C1/CGroup B also the value 529. why is it not doing it in this case?

This only happens when I put columns from one the dimension table in the matrix. All the other endless combination work properly (also which are not showon here). Ffor example when I take a column from the product table like in the matrix on botton left my world is in order. 😄

It really feels like I begin from the start so I hope you can free me! 😄

Best regards

Michael

[parry2k]

@Mikelytics glad you find it useful and clarified some concepts. And also thanks for subscribing the channel. Cheers!

[parry2k · Accepted solution]

@Mikelytics First, great question and well explained. The result you are seeing is correct, and it is happening because of auto-exist.  When columns from the same table are used then auto-exist kicks in, in this case, the customer group and the customer column belong to a single table.

If you create a seperate dimension table for the customer group and set a direct relationship with the sales table, and if you use this new dimension table in measure and in the matrix visual, you will not see this issue.

I hope this answer is helpful. If you have further questions, please feel free to reach out.

👉Learn Power BI   to our YT channel - @PowerBIHowTo

[Mikelytics]

Wow! Thank you @parry2k ! The key word auto-exist was exactly what I was looking for. You helped me in two ways: Confirming that there is really something special ongoing as well as directly providing the solution. Awesome and thank you again! Directly started to follow your YT-channel!

Regarding the topic I also found a good sqlbi article using the key word auto-exist which goes deep into the topic. Understanding DAX Auto-Exist - SQLBI

Have good start into the week!

Best regards

Michael

[lbendlin]

Also read this : Filter Arguments in CALCULATE - SQLBI  it explains how the syntax sugar is replaced in the engine.

[LQuedas]

Hey @Mikelytics ,

Can you check if the following change works for you?

Sales Amount Group A with ALL on Group Column= 


var _CustomerID = Fact_Sales[FK_CustomerID]

Return CALCULATE(
    [Sales Amount],
    FILTER(
        ALL(Dim_Customer[CategoryGroup]),
        AND(Dim_Customer[CategoryGroup] = "CGroup A",Dim_Customer[PK_CustomerID]=_CustomerID)
    )
)

Cheers, LQ

[Mikelytics]

Hi @LQuedas 

Thank you very much and I appreciate your feedback!

The Measure you provided can not work since you refer in the variable on a column without using any function

2nd it is really not about changing anything. Its about understanding the fundamental behaviour of ALL() in combination with CALUCATE() in the given setup. I only look for the existing setup why in the red highlighted fields no values are shown. As you can see in all other matrixes there is a behaviour that existing filter context can be overwritten, excpet in this single combination.

Best regards

Michael