Forum Discussion
Calculate sum using different relationships based on condition
Hello everyone,
I'm trying to build a rather complicated DAX measure but I have no luck getting the expected the result. Here's a simplified description of my data:
I have a table that contains bookings and the amount of the booking. There is a company_id column which links to a company. Each booking has a booking_period_id which links to a booking period table which contains this ID and the corresponding date. booking_period_ids are determined per company so two companies can't have the same booking_period_id but may have the same booking period date. This is not a classical date table though because the dates may not be continuous. Each booking also has an account_schema_id1 and an account_schema_id2 which link to a account schema table. The account_schema_id1 has a matching account_schema_id2 value. Additionally, there is a flag called is_switched. is_switched indicates which account_schema_id should be used for the relationship (i. e. where the sum should be displayed). is_switched is determined on a monthly basis for the company and account_schema_id. The measure I'm trying to build should do the following:
- Identify the last (i. e. most recent) value of the is_switched column for the company and each account_schema_id1 based on the booking period selected; this need to be done on row-level
- If the last/most recent value of is_switched for the company and account_schema_id1 is 1 then display the sum using the relationship that links account_schema_id2 to the account schema table (this is the inactive relationship); the sum should only sum up column values of amount for which the overall most recent value (where row booking period <= selected booking period) of is_switched is 1
- If the last/most recent value of is_switched for the company and account_schema_id1 is 0 then display the sum using the relationship that links account_schema_id1 to the account schema table (this is the active relationship); the sum should only sum up column values of amount for which the overall most recent value (where row booking period <= selected booking period) of is_switched is 0
In other words, if the most recent value (based on the selected booking period) of the is_switched column for a company and account_schema_id is 1 then I want to filter the bookings table to these rows, sum them up and display them for account_schema_id2.
If the most recent value (based on the selected booking period) of the is_switched column for a company and account_schema_id is 0 then I want to filter the bookings table to these rows, sum them up and display them for account_schema_id1.
Here's an example and the expected result:
Booking table:
| booking_id | amount | company_id | booking_period_id | account_schema_id1 | account_schema_id2 | is_switched |
| 1 | 10 | 1 | 2 | 1 | 3 | 1 |
| 2 | 5 | 1 | 4 | 1 | 3 | 0 |
| 3 | 10 | 1 | 1 | 1 | 3 | 1 |
| 4 | 5 | 1 | 1 | 2 | 4 | 1 |
| 5 | 10 | 1 | 2 | 2 | 4 | 0 |
| 6 | 5 | 2 | 7 | 5 | 8 | 0 |
| 7 | 10 | 2 | 5 | 5 | 8 | 1 |
| 8 | 5 | 2 | 7 | 5 | 8 | 0 |
| 9 | 10 | 2 | 5 | 6 | 7 | 1 |
| 10 | 5 | 2 | 5 | 6 | 7 | 1 |
Booking period table:
| booking_period_id | booking_period |
| 1 | 1/1/2023 |
| 2 | 1/2/2023 |
| 3 | 1/3/2023 |
| 4 | 1/4/2023 |
| 5 | 1/1/2023 |
| 6 | 1/2/2023 |
| 7 | 1/3/2023 |
Account schema table:
| account_schema_id | name | top_name |
| 1 | Test1 | TestA |
| 2 | Test2 | TestB |
| 3 | Test3 | TestA |
| 4 | Test4 | TestA |
| 5 | Test5 | TestA |
| 6 | Test6 | TestB |
| 7 | Test7 | TestA |
| 8 | Test8 | TestA |
When a user selects the booking period 01/02/2023 I expect the following behavior for company 1:
- For company 1 the last is_switched value for account_schema_id1 where the id is 1 is 1
- For company 1 the last is_switched value for account_schema_id1 where the id is 2 is 0
- Sum all amounts where last is_switched value is 1 and use the relationship account_schema_id2 to account_schema_id (booking_ids 1, 2, 3)
- Sum all amounts where last is_switched value is 0 and use the relationship account_schema_id1 to account_schema_id (booking_ids 4, 5)
The result for company 1 should be:
| top_name | name | sum of amount (measure) |
| TestA | Test1 | 0 |
| TestB | Test2 | 15 |
| TestA | Test3 | 25 |
| TestA | Test4 | 0 |
| TestA | Test5 | 0 |
| TestB | Test6 | 0 |
| TestA | Test7 | 0 |
| TestA | Test8 | 0 |
Only using the top_name field, the result should look like this:
| top_name | sum of amount (measure) |
| TestA | 25 |
| TestB | 15 |
When a user selects the booking period 01/02/2023 I expect the following behavior for company 2:
For company 2 the last is_switched value for account_schema_id1 where the id is 5 is 1
For company 2 the last is_switched value for account_schema_id1 where the id is 6 is 1
Sum all amounts where last is_switched value is 1 and use the relationship account_schema_id2 to account_schema_id
Sum all amounts where last is_switched value is 0 and use the relationship account_schema_id1 to account_schema_id (booking_ids 6, 7, 8, 9, 10)
The result for company 2 should be:
| top_name | name | sum of amount (measure) |
| TestA | Test1 | |
| TestB | Test2 | |
| TestA | Test3 | |
| TestA | Test4 | |
| TestA | Test5 | |
| TestB | Test6 | |
| TestA | Test7 | 15 |
| TestA | Test8 | 20 |
Only using the top_name field, the result should look like this:
| top_name | sum of amount (measure) |
| TestA | 35 |
| TestB | 0 |
6 Replies
- lbendlin
Super User
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information or anything not related to the issue or question.
If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523 Hi obungianer
I don't quite understand the logic of the "is_switched" column, here's the test I'm doing so far, maybe you can try it:
Create two measures as follow
is_switched = SWITCH(TRUE(), MAX([company_id]) = 1 && MAX([account_schema_id1]) = 1, 1, MAX([company_id]) = 1 && MAX([account_schema_id1]) = 2, 0, MAX([company_id]) = 2 && MAX([account_schema_id1]) = 5, 1, MAX([company_id]) = 2 && MAX([account_schema_id1]) = 6, 1)sum of amount1 = CALCULATE(SUM(Booking[amount]), FILTER(Booking, [is_switched] = 1), USERELATIONSHIP(Booking[account_schema_id2], 'Account schema'[account_schema_id]))The result for company 1:
Best Regards,
Yulia XuIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
