Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I'm gaining a better appreciate for DAX and context. There is one topic that still has me a little puzzled. Appreciate some help to reaffirm my understanding (or corrections are welcome) and help with the bits I don't quite get yet...
Data and model
Three tables, one fact (with amounts) and two dimension (colors and locations).
Data within each:
Simple matrix FYI
Sum and count respectively
Calculated Columns
Note - I know I could (should) use RELATEDTABLE or RELATED for these but am testing this theory with simplified examples.
I create the following calculated columns. The results are as expected when 'wrapped' in a CALCULATE that causes a context transition from (calculated column) row to a filter, thanks to the 1-to-many relationships.
In dimColor
Total Amount CALC = calculate(sum(factSales[fAmt]))
Correctly results in Red =11, Green = 19, Blue = 17
In dimLoc
Total Amount CALC = calculate(sum(factSales[fAmt]))
Correctly results in North = 15, South = 21, East = 11
Now, the fact table...
In factSales
Color Name CALC = calculate(values(dimColor[dCname]))
and
Loc Name CALC = calculate(max(dimLoc[dLname]))
Both result in the correct colour and location (values, max or I could have used various other functions).
So, these last two columns in factSales work even though the relationship is many-to-1 and single direction back to the dimension tables, because CALCULATE transitions the row to filter context.
Measures
Tried to replicate above theory.
Measures are:
Count Color noCALC = countrows(dimColor) Count Col from dimLoc = sumx(dimLoc, CALCULATE(countrows(dimColor))) Count Col from factSales = sumx(factSales, CALCULATE(countrows(dimColor)))
and
Count Loc noCALC = countrows(dimLoc) Count Loc from dimColor = SUMX(dimColor, CALCULATE(countrows(dimLoc))) Count Loc from factSales = SUMX(factSales, CALCULATE(countrows(dimLoc)))
Both noCALC ones are incorrect - filter context flows in direction of relationship, measure can be made to work modifying the relationship or using CROSSFILTER().
But, I to understand how to make Count Col from dimLoc and Count Loc from dimColor the same as the similar measure based on iterating factSales.
So my questions:
I greatly appreciate the time taken by those willing to offer help and advice. And truely hope I can repay the favor one day, or pay forward the kindness.
Solved! Go to Solution.
Not an answer, but I've been furiously researching and wanted to share some related and very useful resources that I believe are explaining this. I advise those wanting to understand this better, spend time reading (and re-reading many times over) these:
And some great articles on all things realted to this:
PS I need to read over these many times still, but I am less convinced I have the right expectations in my original post.
Hi
You are almost there - and yes understanding the concept of table expansion is what you need to focus on to understand why this is not working for you.
First get rid of the concept that filters flow from the one side of a relationship to the many side. (Dimensions can filter facts). That is a very simplified explanation and in order to really understand filter context you need to move on the more advanced topic - table expansion.
I will not go into to much detail here about it - you can text me an email if you find it hard to understand (Lasse0509@gmail.com)
Note: I havnt downloaded your pbix file and looked at the data I just think I know what your issue is simply by reading the text here.
Summary:
Tables in DAX are always expanded following a *-1 relationship. So quite often the expanded fact table actually contains almost the entire datamodel (and this has nothing to do with bidirectional filtering fyi).
So the reason why fx. Count color NoCalc is not working is because you want a countrows of DimColor and then filter it by locations in the pivot table. Does the expanded DimColor table contain the columns of DimLoc? No because they cant reach each other through a *-1 relationship. Thats why you see 3 in the rows and on the total. Look at your datamodel and make sure you understand what i just wrote.
Now try to write this formula instead:
Calculate ( Countrows ( DimColor ) ; factSales )
Did it work? If not i have misunderstood what you want. If yes try to study what I did - I parsed the factsales table as a filter argument in calculate. Does the expanded factsales table contain all the columns of both DimColor and DimLoc? Probably yes 😉
Now you know a bit about the magic of table expansion.
Br,
Hi @lasse0509, thanks for your response. I thought this topic might have been rather buried by now 🙂
I have a much better appreciation of Expanded Tables now, and your post has reaffirmed my understanding, thanks.
There was recently a post by Alberto on his SQLBI blog that went a long way to helping explain this as well - https://www.sqlbi.com/articles/expanded-tables-in-dax/
Not an answer, but I've been furiously researching and wanted to share some related and very useful resources that I believe are explaining this. I advise those wanting to understand this better, spend time reading (and re-reading many times over) these:
And some great articles on all things realted to this:
PS I need to read over these many times still, but I am less convinced I have the right expectations in my original post.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
99 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |