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
C-Jac
Helper I
Helper I

Compare rows from other table to make calculated column in first table

Hi, 

 

I have two tables, one with accounts, and one with expenses. In the table with accounts I have an ID, which is linked to the expense table, and I have an account Name, a status and I would like a calculated column which should determine whether or not the account is ready to be closed. 

 

Table with accounts: 

Id   Account   Status   ShouldClose   
1AActive0
2BCompleted0
3CCompleted1
4DCompleted1
5EClosed0

 

This calculated column, marked with orange should be calculated based on the status and on the second table with expenses. If the current Actual and Posted Revenue and Expense are equal to each other, and the Account = Completed, then the flag should be 1. 

 

AccountID    Type   IsCurrent   Rev   Exp   
1Actual1500100
1Posted1500100
2Actual1600150
2Posted160050
3Actual010001000
3Posted0500500
3Actual11200400
3Posted11200400
4Actual11600800
4Posted11600800
5Actual1900100
5Posted1900100

 

E.g. for AccountID 3: 

Current Rev: 

Actual 1200 - Posted 1200 = 0

Current Exp: 

Actual 400 - Posted 400 =

 

Here the flag should be 1, because the Account is Completed. 

 

E.g. for Account 2: 

Current Rev: 

Actual 600 - Posted 600 = 0

Current Exp: 

Actual 150 - Posted 50 = 100

 

Here the flag should be 0, since both are not equal to 0. 

 

I hope this makes sense, and that you can help me 🙂 

1 REPLY 1
amitchandak
Super User
Super User

@C-Jac , Try a new column in Table1

new column =
var _1 = sumx(filter(Table2, Table2[IsCurrent] =1 && Table2[AccountID] = Table1[ID] && Table1[Type] ="Actual"), Table2[REV]+ Table2[Exp])
var _2 = sumx(filter(Table2, Table2[IsCurrent] =1 && Table2[AccountID] = Table1[ID] && Table1[Type] ="Posted"), Table2[REV]+ Table2[Exp])
return
if(_1 =_2 ,1,0)

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
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.

June 2025 community update carousel

Fabric Community Update - June 2025

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