cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JustinDoh1
Post Patron
Post Patron

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

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.

JustinDoh1_0-1634254926219.png

 

 

2nd issue:

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

JustinDoh1_0-1634255022477.png

 

 

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

JustinDoh1_2-1634250981848.png

 

 

 

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

JustinDoh1_1-1634253958393.png

 

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)?  

 

JustinDoh1_0-1634255901764.png

 

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

 

 

Thanks.

1 ACCEPTED SOLUTION
ERD
Super User
Super User

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

ERD_0-1634317370880.pngERD_1-1634317389706.png

ERD_2-1634317398890.png

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

View solution in original post

4 REPLIES 4
ERD
Super User
Super User

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

ERD_0-1634317370880.pngERD_1-1634317389706.png

ERD_2-1634317398890.png

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

View solution in original post

@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? 

@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.

@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?

JustinDoh1_0-1634326566758.png

Thank you.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.