Forum Discussion

tvaishnav's avatar
tvaishnav
Helper IV
7 years ago

DAX help

 

Hello everyone,

 

I have a following model in SSAS.

 

 

I want to add a new calculated column to Phase Totals table. Both the tables Phase totals and Job totals have a column called Job Number. I want to pull Cost from Job Total tables into Phase totals where there is a Job number match. How should I go about it? 

Need Help

4 Replies

  • jtownsend21's avatar
    jtownsend21
    Responsive Resident
    7 years ago

    Can you change the relationship between Job Totals and Data to be birectional? If so, you can create the following measure on your Phase Totals Table: 

    Job Totals Cost =
    SUM('Job Totals'[Cost])

    If that isn't an option then you can try a lookup like so: 

    Job Totals Cost = 
LOOKUPVALUE(
    SUM('Job Totals'[Cost]),
    'Job Totals'[Job Number],
    'Phase Totals'[Job Number]
)

    However I am not sure about using a sum in a lookupvalue. You may need to use the CALCULATETABLE function, but I have not used that before. 

    • tvaishnav's avatar
      tvaishnav
      Helper IV
      7 years ago

      I modified the lookup formula that you gave and tried using it.

       

      =LOOKUPVALUE(
    'Job Totals'[Cost],
    'Job Totals'[Job Number],
    'Phase Totals'[Job Number]
)

      This worked okay except that I have a few blanks. I have multiple instances of the Job Number in Phase Totals table. Lookup is able to pull Cost from Job totals for all the repeated instances (it will be same value) but leaves some of those blanks. Can you think of any reason why I might be getting blanks?

      • jtownsend21's avatar
        jtownsend21
        Responsive Resident
        7 years ago

        That's odd... Can I assum that all the Job Numbers on the Phase table are also on the Job Totals table?