Forum Discussion

B_Real's avatar
B_Real
Icon for Advocate IV rankAdvocate IV
9 years ago
Solved

DAX query on data warehouse tables

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?

  • 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
        )

2 Replies

  • Vvelarde's avatar
    Vvelarde
    Icon for Community Champion rankCommunity Champion

    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
        )
    • B_Real's avatar
      B_Real
      Icon for Advocate IV rankAdvocate IV

      Ah the RELATED() function. Thanks man :-)