Forum Discussion
How to create a calculated column with same value in every row with a calculated value
Hi Anonymous
DAX works a little differently in Calculated Columns because of Row Context. When using DAX in a measure, you have to be more aware of it's Filter Context. Row Context allows you to reference a 'naked' column in a row without an aggregating funtion, because DAX knows that row and column you mean.
When used in a Calculated Column the Calculate function has an interesting behaviour, it 'converts' the value of each row into a Filter Context for the calculation. In your case, for the first row this creates a filter context of rDate = -2, dayOfWeek = 0, ...etc. So to get the same value for every row, you need to first clear the relevant filter context in the Calculate function.
Something like:
thisFiscalWeek = CALCULATE(MAX('Date Hierarchy'[fiscalWeek]), ALL('Date Hierarchy'), 'Date Hierarchy'[rDate] = 0)
Generally (depending on your use of the column) it is usually better to use a Measure instead of the Calculated Column.
These references might be useful:
Hi Anonymous
DAX works a little differently in Calculated Columns because of Row Context. When using DAX in a measure, you have to be more aware of it's Filter Context. Row Context allows you to reference a 'naked' column in a row without an aggregating funtion, because DAX knows that row and column you mean.
When used in a Calculated Column the Calculate function has an interesting behaviour, it 'converts' the value of each row into a Filter Context for the calculation. In your case, for the first row this creates a filter context of rDate = -2, dayOfWeek = 0, ...etc. So to get the same value for every row, you need to first clear the relevant filter context in the Calculate function.
Something like:
thisFiscalWeek = CALCULATE(MAX('Date Hierarchy'[fiscalWeek]), ALL('Date Hierarchy'), 'Date Hierarchy'[rDate] = 0)
Generally (depending on your use of the column) it is usually better to use a Measure instead of the Calculated Column.
These references might be useful:
Thank you very much for explaining this thoroughly!