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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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.

View solution in original post

3 REPLIES 3
Wilson_
Memorable Member
Memorable Member

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.

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.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.