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.
I have a fact table that contains measurements for a person, that looks like this:
And a dimension table that holds information about the person, such as their date of birth, like so:
I want to build a report that shows the measurements at the age of the person when the measurement was taken.
My approach thus far is to create a new column in my fact table that calculates the age of the person at the date the measurement was taken, but it won't let me introduce the dimension table in my DAX query:
Age at Measurement = DATEDIFF([FactTable].[Date of Measurement], [but I can't put dimension table in here])
The query will only consider other columns from the fact table that the column is being added to.
Obviously this problem scales up to pretty much any query that combines multiple tables.
Any ideas on how to get around this?
Solved! Go to Solution.
hi @B_Real
Use this Dax in a new column in Fact Table
Age at Measurement = DATEDIFF ( RELATED ( Persons[Date of Birth] ), FactTable[Date of Measurement], YEAR )
hi @B_Real
Use this Dax in a new column in Fact Table
Age at Measurement = DATEDIFF ( RELATED ( Persons[Date of Birth] ), FactTable[Date of Measurement], YEAR )
Ah the RELATED() function. Thanks man 🙂
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 |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
106 | |
105 | |
86 | |
72 |