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...
Need Help
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? 

      • tvaishnav's avatar
        tvaishnav
        Helper IV
        7 years ago

        Yes. You can assume that.

         

        In fact, it started working fine once I changed the relationship as follows. 

         

        Any ideas why?