Forum Discussion

pmdci's avatar
pmdci
Advocate V
10 years ago
Solved

DAX: relating a field from another table in a function

Hi,
 
I created the following DAX calculated column, which I am using across different tables.
 
=(YEAR(Today())-YEAR(DATEFIELD]))*12+MONTH(Today())-MONTH([DATEFIELD])
 
This field calculates how many months have elapsed from the date found in DATEFIELD until the time the model is processed. It works quite well, except for one little caveat: The DATE FIELD must exist within the table itself.
 
But what if I want to relate to a date found at a date dimension table? Say that I only have a datekey in the table I want to hold my calculated field, and I want to refer to the date field found in the date dimension table. How would I go about it? I tried playing with the RELATED() function but I only got errors.
 
Regards,
P.

Need Help
  • pmdci's avatar
    pmdci
    10 years ago

    Neuro81 and ankitpatira

     

    Sorry for the trouble, but I managed to fix it in the end

     

    =(YEAR(Today())-YEAR(RELATED('Availability Calendar'[Date])))*12+MONTH(Today())-MONTH(RELATED('Availability Calendar'[Date]))

     

    The problem is that I got lost with the number of parenthesis (how lame!) So I deconstructed the formula and use a replace function to make it work.

     

    Wish I could delete this post altogether. Again sorry for wasting your time! :smileysad::smileymad:

8 Replies

  • ankitpatira's avatar
    ankitpatira
    Community Champion
    10 years ago

    pmdci You can make use of RELATED function. It will work since you have relation between two tables.

  • Neuro81's avatar
    Neuro81
    Helper I
    10 years ago

    What errors did you get?
    also did you create a relationship between the two tables in the diagram view?

    • pmdci's avatar
      pmdci
      Advocate V
      10 years ago

      Neuro81 There is a relationship, yes. Between the fact table in which the calculated column is located, and a Date Dimension table. The linked fields are called DATEKEY in both tables.

       

      Here is what I tried:

       

      =(YEAR(Today())-YEAR(RELATED('Availability Calendar'[Date]))*12+MONTH(Today())-MONTH(RELATED('Availability Calendar'[Date]))

       

      But Visual Studio gives me a syntax error message.

    • pmdci's avatar
      pmdci
      Advocate V
      10 years ago

      Neuro81 and ankitpatira

       

      Sorry for the trouble, but I managed to fix it in the end

       

      =(YEAR(Today())-YEAR(RELATED('Availability Calendar'[Date])))*12+MONTH(Today())-MONTH(RELATED('Availability Calendar'[Date]))

       

      The problem is that I got lost with the number of parenthesis (how lame!) So I deconstructed the formula and use a replace function to make it work.

       

      Wish I could delete this post altogether. Again sorry for wasting your time! :smileysad::smileymad:

      • Neuro81's avatar
        Neuro81
        Helper I
        10 years ago

        pmdci
        Sometimes its best to talk it out.

        Whenever I get stuck I usually take a walk and try not to think of the problem and a lot of the time the solution just comes.

        Its good that you take the time to try and fix the problem yourself instead of just waiting till someone comes to help you!!