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

How to show column with no value in this case?

I am sharing my Pbix file here.

 

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?

 

 

Thanks.

1 ACCEPTED SOLUTION
HotChilli
Super User
Super User

Do you want the column (in Budget visual) to show with all zeroes (and use that in the variance calculation)?

 

I suggest deleting the relationship between actual and budget.

Create a new dimension table with the distinct values of JoinKey,Payer, Facility.  Create relationships with budget and actual tables on Joinkey.

Put Facility from this dimension table in the rows of each visualisation.

Create a measure for the Budget visual (MeasureTest = COUNT(Budget[ID]) + 0).

---

Well, that's just my quick thoughts after having a look at the pbix.

Let me know how it goes.

View solution in original post

2 REPLIES 2
HotChilli
Super User
Super User

Do you want the column (in Budget visual) to show with all zeroes (and use that in the variance calculation)?

 

I suggest deleting the relationship between actual and budget.

Create a new dimension table with the distinct values of JoinKey,Payer, Facility.  Create relationships with budget and actual tables on Joinkey.

Put Facility from this dimension table in the rows of each visualisation.

Create a measure for the Budget visual (MeasureTest = COUNT(Budget[ID]) + 0).

---

Well, that's just my quick thoughts after having a look at the pbix.

Let me know how it goes.

View solution in original post

@HotChilli Thanks for help. This is a great approach. Your code on the measure (MeasureTest = COUNT(Budget[ID]) + 0) saved my day as well 🙂 .

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.