Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I have a question regarding building a model with unary operators. I have the following dimension:
Metric | MetricAbbrevated | AccountNumber | Order |
Room Revenue | RoomRev | 10512051 | 1 |
Occupancy | Occ | 2 | |
ADR | ADR | 3 | |
RevPAR | RevPAR | 4 | |
Rooms Sold | RS | 9105120RS | 5 |
Rooms Available | RA | 9105120RA | 6 |
I am going to have a fact table that has a column AccountNumber and a column Amount. I want to use the Metric as a slicer and have one measure. The measure will be the sum of the Amount column in the fact table. As you can see, three of the metrics do not have an account number. I need it so that when the user slices on Occupancy, the model with use unary operators to divide the sum of Rooms Sold and Rooms Available. Slice on ADR gives you a division of the sum of room revenue and rooms sold. Slice on RevPAR gives you a division of room revenue and rooms available. Slicing on the others just gives you the sum of amounts that correspond to the account number.
Does anyone have any tips on how to accomplish this?
Solved! Go to Solution.
Hi @vega,
Try this demo please. (Many details are in the file.)
Measure = IF ( HASONEVALUE ( 'Table1'[Metric] ), IF ( VALUES ( Table1[Metric] ) = "Occupancy", DIVIDE ( CALCULATE ( SUM ( Table2[Amount] ), FILTER ( ALL ( Table2 ), 'Table2'[AccountNumber] IN { "9105120RS", "9105120RA" } ), ALL ( Table1 ) ), 2, 0 ), IF ( VALUES ( Table1[Metric] ) = "ADR", DIVIDE ( CALCULATE ( SUM ( Table2[Amount] ), FILTER ( ALL ( Table2 ), 'Table2'[AccountNumber] IN { "10512051", "9105120RS" } ), ALL ( Table1 ) ), 2, 0 ), IF ( VALUES ( Table1[Metric] ) = "RevPAR", DIVIDE ( CALCULATE ( SUM ( Table2[Amount] ), FILTER ( ALL ( Table2 ), 'Table2'[AccountNumber] IN { "10512051", "9105120RA" } ), ALL ( Table1 ) ), 2, 0 ), SUM ( Table2[Amount] ) ) ) ), BLANK () )
Best Regards,
Dale
Hi @vega,
Try this demo please. (Many details are in the file.)
Measure = IF ( HASONEVALUE ( 'Table1'[Metric] ), IF ( VALUES ( Table1[Metric] ) = "Occupancy", DIVIDE ( CALCULATE ( SUM ( Table2[Amount] ), FILTER ( ALL ( Table2 ), 'Table2'[AccountNumber] IN { "9105120RS", "9105120RA" } ), ALL ( Table1 ) ), 2, 0 ), IF ( VALUES ( Table1[Metric] ) = "ADR", DIVIDE ( CALCULATE ( SUM ( Table2[Amount] ), FILTER ( ALL ( Table2 ), 'Table2'[AccountNumber] IN { "10512051", "9105120RS" } ), ALL ( Table1 ) ), 2, 0 ), IF ( VALUES ( Table1[Metric] ) = "RevPAR", DIVIDE ( CALCULATE ( SUM ( Table2[Amount] ), FILTER ( ALL ( Table2 ), 'Table2'[AccountNumber] IN { "10512051", "9105120RA" } ), ALL ( Table1 ) ), 2, 0 ), SUM ( Table2[Amount] ) ) ) ), BLANK () )
Best Regards,
Dale
This gets the job done with some tweaking. Question for you, can you explain why the ALL(Table1 is needed as a filter for the calculate sum?
Hi @vega,
The ALL(Table1) removes the filters from Table1.
Best Regards,
Dale
yes all this can be done, if you share sample data from both of your tables, will help to provide the solution.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |