Forum Discussion
Get data from another table where condition is met
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?
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.
2 Replies
- amitchandak
Super User
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"))
v-kkf-msftCommunity Support
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.
