Calculate value using IF condition across multiple tables

[Anonymous]

I have data across 3 tables.

First table has the mapping codes for the other 2 tables

AIMSCode	CHRISCode	HealthService
1000	8000	Alfred Health
2000	9000	Monash Health

Second table provides the data for the AIMS system that stores data for the the number of beds available

AIMSCode	Type	Value
1000	beds available	8
2000	beds available	10

Third table provides information on how many beds are currently used

CHRISCode	Type	Value
8000	Occupied Beds	15
9000	Occupied Beds	6

Basically what I'm trying to achive is [beds available]- [occupied beds] for each health service, and if the value is less than zero then ignore but add up the health services that are greater than zero.

So essentially I want the measure to return 4 in this instance (10-6). 

I have been wracking my brain trying to figure this out but have been completely unsuccessful. Thank you for any help!

[VahidDM · Accepted solution]

Hi @Anonymous 

Please download this file and let me know if it does not cover your request:

https://gofile.io/d/ebakXo

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos ✌️!!

[Anonymous]

awesome! this has given me the basic idea on how to do this!

[wdx223_Daniel]

assume you have create one to many replationship between those tables, and try this code

NewMeasure=MAX(BLANK(),MAXX(Table1,CALCULATE(SUM(Table2[Value])-SUM(Table3[Value]))))