Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hello,
I'm stuck on a DAX problem in Power BI and could use some help. I have a table with reservation data, and I'm trying to count the number of unique guests for each booking reference while making sure to only count each guest once, even if they booked multiple hotels.
For example, if I normally do SUMX(Reservation, Adult + Child). That would have returned 4 but the correct result should be 2 only.
RefID | ResID | HotelKey | OperatorKey | Booking Ref | Lead Guest | Adult | Child |
2193049 | 1871763 | 10000255 | 16 | 4688459 | Lead Guest225244 | 2 | 0 |
2193048 | 1871763 | 10000771 | 16 | 4688459 | Lead Guest225244 | 2 | 0 |
When trying it in DAX studio with specific rows that falls in the criteria, I seem to get the correct numbers.
Here's the DAX expression I've tried, but it's giving me unexpected results:
Pax =
VAR _Reservation =
SUMMARIZE (
FILTER ( 'Reservation', 'Reservation'[Status] <> "Can" ),
'Reservation'[OperatorKey],
'Reservation'[Booking Ref],
'Reservation'[Lead Guest],
'Reservation'[Adult],
'Reservation'[Child]
)
RETURN
SUMX ( _Reservation, 'Reservation'[Adult] + 'Reservation'[Child] )
Incorrect result:
The correct total should be 553,536
Here's the PBIX file
https://1drv.ms/u/s!Aj0trVkg_AXisVE0uWATzj22I1-f?e=G3hvPr
Solved! Go to Solution.
Justivan,
Here's a link to my updated pbix.
For this calculation only, it sounds like you need to filter out all the OperatorKey, Booking Ref, Lead Guest, Adult, Child combinations except for the earliest In Date. I added a T/F calculated column to your Reservations table to note whether it is the earliest In Date for that combination, then included that flag in your Pax measure.
The numbers do change a bit, but the visual totals the way you would expect now that the cross-year bookings issue has been resolved. (I only validated the change in the 2025 number, so you may want to validate more thoroughly.)
Hopefully this is the solution you were looking for. 🙂
----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)
P.S. Need a more in-depth consultation for your Power BI data modeling or DAX issues? Feel free to hire me on Upwork or DM me directly on here! I would love to clear up your Power BI headaches.
Proud to be a Super User! | |
Hi Justivan,
I'll defer to you on whether it's legitimate or not in your data, but this must mean some OperatorKey, Booking Ref, Lead Guest, Adult, Child combinations appear in multiple years.
If you add Calendar Year to your _Reservation variable, the calculation works as you expect:
Pax =
VAR _Reservation =
SUMMARIZE (
FILTER ( 'Reservation', 'Reservation'[Status] <> "Can" ),
'Date'[Calendar Year],
'Reservation'[OperatorKey],
'Reservation'[Booking Ref],
'Reservation'[Lead Guest],
'Reservation'[Adult],
'Reservation'[Child]
)
VAR Result =
SUMX ( _Reservation, 'Reservation'[Adult] + 'Reservation'[Child] )
RETURN Result
----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)
P.S. Need a more in-depth consultation for your Power BI issue? Feel free to hire me on Upwork or DM me directly on here! I would love to clear up your Power BI headaches.
Proud to be a Super User! | |
Hi Wilson,
Sorry for the late reply as I was in a holiday. You are right and that's going to be a problem. Our business requires that it only consider it once based on the first day of the 'In Date'. So for example, with rows like this, pax should be counted only in 30/12/2023. Any idea how this should be done?
RefID | ResID | HotelKey | OperatorKey | Booking Ref | Lead Guest | In Date | Out Date | Room Type | Days | Meal | Adult | Child |
188477 | 148022 | 10001072 | 3 | LVWBP | Lead Guest95490 | 30/12/2023 | 09/01/2024 | Superior Room - Ocean View | 10 | BB | 2 | 0 |
188895 | 148022 | 10001071 | 3 | LVWBP | Lead Guest95490 | 05/01/2024 | 09/01/2024 | Superior Room | 4 | BB | 2 | 0 |
Justivan,
Here's a link to my updated pbix.
For this calculation only, it sounds like you need to filter out all the OperatorKey, Booking Ref, Lead Guest, Adult, Child combinations except for the earliest In Date. I added a T/F calculated column to your Reservations table to note whether it is the earliest In Date for that combination, then included that flag in your Pax measure.
The numbers do change a bit, but the visual totals the way you would expect now that the cross-year bookings issue has been resolved. (I only validated the change in the 2025 number, so you may want to validate more thoroughly.)
Hopefully this is the solution you were looking for. 🙂
----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)
P.S. Need a more in-depth consultation for your Power BI data modeling or DAX issues? Feel free to hire me on Upwork or DM me directly on here! I would love to clear up your Power BI headaches.
Proud to be a Super User! | |
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
90 | |
84 | |
70 | |
49 |
User | Count |
---|---|
143 | |
121 | |
112 | |
59 | |
58 |