Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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:
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:
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 |
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 Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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-...
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/1447...
Hello @lbendlin,
I edited the message to include HTML tables. You can also find an example with the expected result.
I think we're missing the Company table
Not sure where you are going with the is_switched but likely you can use inactive relationships.
The company table is just a simple dimension:
company_id | name |
1 | company 1 |
2 | company 2 |
The is_switched column indicated whether the active or inactive relationship should be used to calculate the total sum and for which top_name and name to display the value in a visual. Please review my question and examples. It explains the purpose of the is_switched column.
I'm already using inactive and active relationships.
I reviewed your questions and examples and I don't understand the logic. I hope someone else can help you further.
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
24 | |
10 | |
6 | |
6 | |
6 |
User | Count |
---|---|
29 | |
11 | |
11 | |
10 | |
6 |