Match 2 Tables using Measure (with conditions)

[wongts_nms]

Hi, Expert,

I wish to create a measure in the Power BI to match the following condition but unable to get the correct matching:

I have 2 tables:  Bank and GL

In the bank table, there is a deposit on 1/4/2025 amounted to RM410 = column Bank[In]

In the GL table, there are 2 transactions on 1/4/2025, one RM214.80, another one RM195.20 which make up of the total of RM410 and these 2 transactions both referenced by:
a) 410.00 (numeric) in the column GL[Clean Ref Numeric]
b) 214.80 (one row) and 195.20 (another row) in the column GL[Debit]

My Goal is:
Create a DAX measure that matches Bank deposits (Bank[In]) to the grouped GL entries based on total amount (GL[Debit]) and reference number in GL[Clean Ref Numeric].

I created the below DAX but still unable to match it correctly:

IsBankMatched =
VAR SelectedBankIn = SELECTEDVALUE(Bank[In])
VAR GLTotal = CALCULATE(SUM(GL[Debit]),
FILTER(GL,GL[Clean Ref Numeric] = SelectedBankIn))
RETURN IF(SelectedBankIn = GLTotal, "Matched", "Not Matched")

Appreciate for the advices.

[v-echaithra]

Hi @wongts_nms ,

We would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.
If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Regards,
Chaithra E.

[sevenhills]

Try and see if this works:

IsBankMatched =
VAR _BankIn = SELECTEDVALUE(Bank[In])
VAR _Date = SELECTEDVALUE(Bank[Date])

VAR GLTotal = CALCULATE( SUM(GL[Debit]),
                 FILTER( ALL(GL), GL[Clean Ref Numeric] = _BankIn && GL[Date] = _Date)
              )
-- RETURN IF( NOT ISBLANK(_BankIn) && NOT ISBLANK(_Date) && ABS(GLTotal - _BankIn) < 0.01,
-- RETURN IF ( HASONEVALUE ( Bank[In]) && HASONEVALUE(Bank[Date]) && ABS(GLTotal - _BankIn) < 0.01,
RETURN IF(   ABS(GLTotal - _BankIn) < 0.01,
            "Matched", "Not Matched"
       )

ALL(GL) or ALLSELECTED(GL) based on your needs, if the above formula works!

[wongts_nms]

Hello, BBF,

Thank you so much for your prompt advices.  I have tried the both solutions but still unable to match the Bank deposits (Bank[In]) to the grouped GL entries based on total amount (GL[Debit]) and reference number in GL[Clean Ref Numeric].  Below are the sample data:

GL Table

Date	Debit	Clean Ref Numeric
02/04/2025	1,144.57	1,580.00
02/04/2025	379.50	1,580.00
02/04/2025	55.93	1,580.00

Date	Debit	Clean Ref Numeric
01/04/2025	214.80	410.00
01/04/2025	195.20	410.00

Bank Table

Date	In
01/04/2025	410.00
02/04/2025	1,580.00

Appreciate any examples or advice!

[BeaBF]

@wongts_nms try with:

IsBankMatched =
VAR SelectedBankIn = SELECTEDVALUE(Bank[In])
VAR GLTotal =
CALCULATE(
SUM(GL[Debit]),
FILTER(
GL,
GL[Clean Ref Numeric] = SelectedBankIn
)
)
RETURN
IF(
NOT ISBLANK(SelectedBankIn)
&& ABS(GLTotal - SelectedBankIn) < 0.01,
"Matched",
"Not Matched"
)

BBF

💡 Did I answer your question? Mark my post as a solution!

👍 Kudos are appreciated

🔥 Proud to be a Super User!

[burakkaragoz]

Hi @wongts_nms ,

You want to match a deposit in your Bank table to grouped transactions in your GL table based on amount and reference number. The reply you received gives you two DAX solutions: one for models where Bank and GL tables have a direct relationship, and another using TREATAS for when there is no relationship.

Key points:

• If you have a relationship on the reference column, use the first DAX formula. It checks if the selected BankIn value matches the sum of Debit in GL for the same reference. If it matches, it returns "Matched", otherwise "Not Matched".
• If there is no relationship, use the second DAX formula with TREATAS. This virtually maps the reference column and applies the same logic.
• Make sure the columns you match on (BankIn and GLClean Ref Numeric) have compatible data types and matching values.

If the measure is not working as expected, double-check for data type mismatches, formatting issues, or context problems in your visual.

If your scenario is more complex, or you need to match on multiple fields or with tolerances, share more details and I can help you adjust the DAX. Let me know if you need an example with your sample data.

[BeaBF]

@wongts_nms  Hi! Try with:

IsBankMatched =
VAR SelectedBankIn = SELECTEDVALUE(Bank[In])
VAR GLTotal =
CALCULATE(
SUM(GL[Debit]),
FILTER(
GL,
GL[Clean Ref Numeric] = SelectedBankIn
)
)
RETURN
IF(
NOT(ISBLANK(SelectedBankIn)) &&
SelectedBankIn = GLTotal,
"Matched",
"Not Matched"
)

If your model doesn’t have a relationship between Bank and GL:

IsBankMatched =
VAR SelectedBankIn = SELECTEDVALUE(Bank[In])
VAR GLTotal =
CALCULATE(
SUM(GL[Debit]),
TREATAS(
{ SelectedBankIn },
GL[Clean Ref Numeric]
)
)
RETURN
IF(
NOT(ISBLANK(SelectedBankIn)) &&
SelectedBankIn = GLTotal,
"Matched",
"Not Matched"
)

BBF

💡 Did I answer your question? Mark my post as a solution!

👍 Kudos are appreciated

🔥 Proud to be a Super User!