Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
obungianer
New Member

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_idamountcompany_idbooking_period_idaccount_schema_id1account_schema_id2is_switched
11012131
2514130
31011131
4511241
51012240
6527580
71025581
8527580
91025671
10525671



Booking period table:

booking_period_idbooking_period
11/1/2023
21/2/2023
31/3/2023
41/4/2023
51/1/2023
61/2/2023
71/3/2023



Account schema table:

account_schema_idnametop_name
1Test1TestA
2Test2TestB
3Test3TestA
4Test4TestA
5Test5TestA
6Test6TestB
7Test7TestA
8Test8TestA


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_namenamesum of amount (measure)
TestATest10
TestBTest215
TestATest325
TestATest40
TestATest50
TestBTest60
TestATest70
TestATest80


Only using the top_name field, the result should look like this:

top_namesum of amount (measure)
TestA25
TestB15

 

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_namenamesum of amount (measure)
TestATest1 
TestBTest2 
TestATest3 
TestATest4 
TestATest5 
TestBTest6 
TestATest715
TestATest820

 

Only using the top_name field, the result should look like this:

top_namesum of amount (measure)
TestA35
TestB0
6 REPLIES 6
Anonymous
Not applicable

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:

vxuxinyimsft_0-1712049582556.png

 

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.

lbendlin
Super User
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-...

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

lbendlin_0-1710622809374.png

Not sure where you are going with the is_switched but likely you can use inactive relationships.

lbendlin_1-1710622869682.png

 

 

 

The company table is just a simple dimension:

company_idname
1company 1
2company 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.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.