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.
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 |
1 | A | Active | 0 |
2 | B | Completed | 0 |
3 | C | Completed | 1 |
4 | D | Completed | 1 |
5 | E | Closed | 0 |
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 |
1 | Actual | 1 | 500 | 100 |
1 | Posted | 1 | 500 | 100 |
2 | Actual | 1 | 600 | 150 |
2 | Posted | 1 | 600 | 50 |
3 | Actual | 0 | 1000 | 1000 |
3 | Posted | 0 | 500 | 500 |
3 | Actual | 1 | 1200 | 400 |
3 | Posted | 1 | 1200 | 400 |
4 | Actual | 1 | 1600 | 800 |
4 | Posted | 1 | 1600 | 800 |
5 | Actual | 1 | 900 | 100 |
5 | Posted | 1 | 900 | 100 |
E.g. for AccountID 3:
Current Rev:
Actual 1200 - Posted 1200 = 0
Current Exp:
Actual 400 - Posted 400 = 0
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 🙂
@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)
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
23 | |
10 | |
10 | |
9 | |
7 |