cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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 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 =

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 🙂

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)

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors