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,
as a DAX beginner I am confused about a behavior (maybe related to context).
Step 1: I created a table "Numbers" with a single column "Value" and the values (rows) 10, 20, 30, 40
Step 2: I created a measure SumOfAll = SUM(Numbers[Value])
--> When I display SumOfAll I get 100 --> that exactly the answer I expect
Step 3: I add a (new) calculated column to my table with SinRowByRow = SIN(Numbers[Value])
--> Again I see the new values (row by row) are the SIN of the corresponding values. Fine!
Step 4: I add a (second) calculated column to my table with SinByMeasure = SIN([SumOfAll])
==> here the confusions starts.
I see that both calculated columns hold identical data.
What I expected was, that the second calculated column always shows me SIN(100) = -0.506 because I assumed that at first SumOfAll is calculated and then SIN(SumOfAll). This is obviously not true.
What's going on here? What are the rules if a mesure is used in a calculated column?
Could someone enlighten me please!
Regards,
jomu
Solved! Go to Solution.
Hi @jomu
--> a calculated column is a calcualtion executed during the data load and stored to a column
--> a measure is a calculation executed during the run time
Then you have to pay attention to the context.
https://www.sqlbi.com/articles/row-context-and-filter-context-in-dax/
Consider the following example:
1. Calculated Column: SumOfAllColumn1 = SUM(Numbers[Value])
2. Measure: SumOfAll = SUM(Numbers[Value])
3. Calculated Column with a Measure: SumOfAllColumn2 = [SumOfAll]
As you can see, 3. produces a different result than 1., because the Measure is filtered on the row.
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.
I recommend this free "Introducing DAX Video Course"
https://www.sqlbi.com/p/introducing-dax-video-course/
Please give Kudos for support.
Thanks for the hints and for the video. I tried and it works as you explained.
The "basic" of my confusion:
--> a calculated column is a sequence of some data (here: numbers)
--> a measure is something like a scalar, a single value based on something (like SUM, AVERAGE, ...) - maybe filters are involved to filter out some rows.
You used CALCULATE(... ALL(...)) for produce a single value.
But it seams to me that this distinction is not really true. Measures also produce sequences ... this is the point I struggle with.
Hi @jomu
--> a calculated column is a calcualtion executed during the data load and stored to a column
--> a measure is a calculation executed during the run time
Then you have to pay attention to the context.
https://www.sqlbi.com/articles/row-context-and-filter-context-in-dax/
Consider the following example:
1. Calculated Column: SumOfAllColumn1 = SUM(Numbers[Value])
2. Measure: SumOfAll = SUM(Numbers[Value])
3. Calculated Column with a Measure: SumOfAllColumn2 = [SumOfAll]
As you can see, 3. produces a different result than 1., because the Measure is filtered on the row.
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.
Thank a lot for your help, it becomes clearer ...
Hi @jomu
change your SumOfAll Measure to
SumOfAll = CALCULATE(SUM(Numbers[Value]), ALL(Numbers))
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.
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 |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |