Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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!
Solved! Go to Solution.
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 ✌️!!
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 ✌️!!
awesome! this has given me the basic idea on how to do this!
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]))))
User | Count |
---|---|
25 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
27 | |
13 | |
11 | |
9 | |
6 |