Where have I gone wrong?

[Christof]

I'm a bit lost and I've probably missed a step somewhere.
I am trying to link my BookingsTable to my FlightTable.
I loaded both tables into the Data Model as connections, then New Blank Query to link the two tables. The BookingsTable is all unique references.
But when I build a pivot by Booking Ref (the key field) to display airline, it just brings through all airlines on the report.

Attached is my example.

Can anyone please help?

Thanks
Chris

 

[Chihiro]

BookingsTable has ALL items listed in Bookings. Therefore, when an column from this table is added to values field with Bookings in row label. This will force pivot table to evaluate for each item. You will need column/measure from FlightTable in values field to collapse this.

Though personally, I'd recommend either basing Bookings list on items only existing in FlightTable. Or just merge Booking onto FlightTable. Depending on your need.

See attached. Where I used Count of Airline in values field and also added Sum of Pax. You can see how the pivot will eliminate where Count of Airline is blank, when Sum of Pax is removed from values field.


Note: Alternately, you could write DAX with USERRELATIONSHIP to override active relationship to Bookings. But then why bother creating Bookings list?