Forum Discussion

wongts_nms's avatar
wongts_nms
New Member
1 year ago
Solved

Match 2 Tables using Measure (with conditions)

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 dep...
  • BeaBF's avatar
    BeaBF
    1 year ago

    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!

  • sevenhills's avatar
    sevenhills
    1 year ago

    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!

burakkaragoz's avatar
burakkaragoz
Icon for Super User rankSuper User
1 year ago

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.
translation and formatting supported by AI