DAX help

[tvaishnav]

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? 

[jtownsend21]

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]

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]

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

[tvaishnav]

Yes. You can assume that.

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

Any ideas why?