measure used in a calculated column

[Anonymous]

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

[mwegener]

I recommend this free "Introducing DAX Video Course"

https://www.sqlbi.com/p/introducing-dax-video-course/

Please give Kudos for support.

[Anonymous]

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.

[mwegener · Accepted solution]

Hi @Anonymous 

--> 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.

[Anonymous]

Thank a lot for your help, it becomes clearer ...

[mwegener]

Hi @Anonymous 

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.