Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello,
Using DAX, I am trying to add a column to a table that, using a dyamic measure, adds hours to the existing timestamp.
Each record in my existing data table contains a timestamp.
The measure I've created returns a decimal value that represents the offset that will be added to the original timestamp to create the correct datetime value for each record.
The final step, (which isn't working) is that I've created a new column in the data set where I am trying to add my dynamic measure to the original timestamp. Unfortunately, when I use my measure in the calculation, the value in the new column doesn't appear to update and instead matches the original timestamp regardless of what I select using the slicer. I've created a card to verify that the output of my measure is the correct decimal number. That being said, when I hard-code a number into the formula for the new column, it updates correctly.
How can I get my dynamic measure to work?
Example:
A record in MyTable[Original Timestamp] = 3/9/2022 11:00:22 PM
Dynamic Measure return value is a decimal: [Time Zone Offset] = -8.0
Formula for new column in MyTable: [Time Zone Timestamp] = MyTable[Original Timestamp] + [Time Zone Offset]/24
Expected Result: 3/9/2022 3:00:22 PM
Incorrect current result: 3/9/2022 11:00:22 PM
Again, I can hardcode the value in the formula (e.g. MyTable[Original Timestamp] + (-8/24)) and this gives the correct result which leads me to believe Power BI is having trouble with my measure.
Any help will be much appreciated!
Solved! Go to Solution.
You cannot do that. Calculated columns are calculated 1 time, at refresh. Never again until the next refresh. They are not like columns in an Excel table that update with each worksheet calc.
You would need to use a visual that returned the timestamp you want as a measure and add your TZ offset there to visually represent the updated timestamp.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingAh, that's frustrating, but it makes sense. Thanks for the reply and your help!
You cannot do that. Calculated columns are calculated 1 time, at refresh. Never again until the next refresh. They are not like columns in an Excel table that update with each worksheet calc.
You would need to use a visual that returned the timestamp you want as a measure and add your TZ offset there to visually represent the updated timestamp.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingUser | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |