Get data from another table where condition is met

[FrederikB]

I have three tables:

Bookings

ID	Some value
1	a
2	b
3	c
4	d

Invoices

ID	TotalRevenue	BookingID
1	20	1
2	20	1
3	30	2
4	30	2

Line items

ID	BookingID	Title	Value
1	1	Service fee	30
2	1	Transport fee	20
3	1	Price of product	10
4	1	Some other title	40

I want to show the booking table and joining the values from the two other tables into the booking table.

I have tried to make a relationship by BookingID (and ID of the booking table), and then gotten some values like the service fee by

Service fee = calculate(sum('Line items'[Value]),'Line items'[data.invoices.lineItems.Title]="Service fee")

and equally for the other values.

But when showing values from the line items table in the booking table, the values are just flat out wrong. If I only show the values by themself (and applying a filter to only see values for bookingID=1), it works like charm.

So I thought a better solution would be to simply bring in the values from the two other two tables into the booking table to display them, but the problem is that it is a one-to-many relationship, so I can't do that. 

But I thought that maybe I can make a query to like (psuedo code below):

service fee = get 'Value' from line items table where 'Title' = 'Service fee'

Is it possible to do this, or would there be a better way to go arround this?

[v-kkf-msft · Accepted solution]

Hi @FrederikB ,

According to my tests, your formula is able to calculate the summary values correctly. 

If you want to attach the underlying data, try merging the two tables in the Power Query Editor.

    

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@FrederikB , Hope there is 1-M join. Try to add filter for measure and check

Service fee = calculate(sum('Line items'[Value]),filter('Line items', 'Line items'[data.invoices.lineItems.Title]="Service fee"))