Calculate the difference on the value from cell (from two tables)

[JustinDoh1]

I am sharing my Pbix file here.

(Sorry I updated original post dramatically after I realized that I had relationship created between two tables, and this question might not be directly related to DAX command only)

I am trying to display column called "Managed Medical Skilled" in "Budget" table.

(I have been trying to fix this for more than a day... and this task is little urgent..  😞   )

So, "Variance" table subtracts value from "Actual" to "Budget".

For example, FacilityCode 810, Hospice:

8 - 6 = 2.

1st issue:

Column "Managed Medical Skilled" column does not show up on "Budget" table.

2nd issue:

Calculation of difference for "Managed Medical Skilled" column does not show up in "Variance" table.

Main reason is that in "Budget" table, there is no data for that column.

For calculation of Variance, I created a joined key called "JoinKey", and created a relationship between two tables ("Acutal" & "Budget").

I created a separate table called "Payer" with all PayerName values.

Is there anyway to modify the relationship between "Payer" table and "Budget" table so that all PayerName values (including "Managed Medical Skilled") show up in "Budget" table (and it might possibly resolve the issue)?  

How do we go about resolving these two issue in DAX or in table relationship?

Thanks.

[ERD · Accepted solution]

Hi @JustinDoh1 ,

From what I can see:

1. Create another table with distinct FacilityCode values
2. Join this table with your fact tables (delete the relationship on JoinKey columns)
3. Use FacilityCode column from FCODE table in your visuals

If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.

[JustinDoh1]

@ERD I was able to finish the task with you advice. It was brilliant. I have a quesiton though. I am curious why we do not use JoinKey as a key for the dimention table, but use FacilityCode as a key. Logically, I think commom key would be JoinKey, no? 

[ERD]

@JustinDoh1 ,

1) It is not recommended to join fact tables directly

2) You use FacilityCode in your visuals. There might be different facility codes in each table.

If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.

[JustinDoh1]

@ERD Actually, I updated the Pbix file with

3. Use FacilityCode column from FCODE table in your visuals.

I have attached it on this link.

Now, only issue is "Managed Medical Skiled" column does not show up on the "Budget" table with null value.

Is there anyway we could make it happen?

Thank you.