Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get 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

Reply
justivan
Helper II
Helper II

Incorrect Total

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.

RefIDResIDHotelKeyOperatorKeyBooking RefLead GuestAdultChild
2193049187176310000255164688459Lead Guest22524420
2193048187176310000771164688459Lead Guest22524420


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:

justivan_0-1712520692514.png

 

The correct total should be 553,536

Here's the PBIX file

https://1drv.ms/u/s!Aj0trVkg_AXisVE0uWATzj22I1-f?e=G3hvPr 

1 ACCEPTED 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.

 

Wilson__0-1713116360921.png

 

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.




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





View solution in original post

3 REPLIES 3
Wilson_
Super User
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.




Did I answer your question? Mark my post as a solution!

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?

RefIDResIDHotelKeyOperatorKeyBooking RefLead GuestIn DateOut DateRoom TypeDaysMealAdultChild
188477148022100010723LVWBPLead Guest9549030/12/202309/01/2024Superior Room - Ocean View10BB20
188895148022100010713LVWBPLead Guest9549005/01/202409/01/2024Superior Room4BB20

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.

 

Wilson__0-1713116360921.png

 

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.




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.