Forum Discussion
Calculate value using IF condition across multiple tables
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!
Hi Anonymous
Please download this file and let me know if it does not cover your request:
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos ✌️!!
3 Replies
- wdx223_Daniel
Community Champion
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]))))
- VahidDM
Super User
Hi Anonymous
Please download this file and let me know if it does not cover your request:
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos ✌️!!
