Measure different than Calculated Column, but this works... why?

[MarkD1722]

Hello, community.

I tried making a calculated column for a date value so that I could create a hierachy used for time dimensions.  When I first created a measure, the date calculation produced the expected the results.  When I made the column using the same exact formula/calculation, however, I got a single value for all rows...which appeared to be the average or something.  I found plenty of posts/threads on this forum explaining how measures and columns work differently.  So, this led me to try breaking up the formula into measures and then calculating the column from those component measure...and that worked!  Basically, I have a DATE and a DURATION.   In the original measure, there is a variable to deal with the case that no DATE exists and another variable deals with the case if no DURATION (in weeks) if a duration doesn't exist. 

Here are the variables:

VAR Duration1 = IF(ISBLANK(MAXX('PROJECT LIST','PROJECT LIST'[Duration]))||MAXX('PROJECT LIST','PROJECT LIST'[Duration])=0, 1, (MAXX('PROJECT LIST','PROJECT LIST'[Duration])))

VAR ApproveDate1 = IF(ISBLANK(SELECTEDVALUE('PROJECT LIST'[Approve Date])), TODAY(), MAXX('PROJECT LIST','PROJECT LIST'[Approve Date]))

//RETURN

ApproveDate1 + (Duration1*7)  //(to add days)

Breaking out each variable into its own measure, the final Column calculation is simply:

DATE measure + (DURATION measure*7) //(to add days)

Why did breaking it up work for the column rather than having the variables which you would think should do the same thing?

[Anonymous]

Hi @MarkD1722 

Maybe you can try: CALCULATE(SELECTEDVALUE())

The CALCULATE performs the context transition:

CALCULATE – DAX Guide

For example, here's the sample data:

Then add a calcualted column:

Column = SELECTEDVALUE('Table'[ID])

Then add a CALCULATE():

Column = CALCULATE(SELECTEDVALUE('Table'[ID]))

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

When I made the column using the same exact formula/calculation

That will fail. Columns don't have filter context. They have no idea what "SELECTEDVALUE"  means.  

By using MAXX you  are creating row contexts, even for measures.