Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Compare two columns in different tables

I would like to compare two columns in different tables using a measure (and not a calculated column). I made a simple example to illustrate my problem:

 

Data Model

Table A

Table B

Central

Problem

If I make a table visualization of 'Table A'[Value_A] and 'Table B'[Value_B], I get:

I'm not sure why the row (C, C) only appears once instead of twice, but I'll return to that question later. Now, I want to create a measure that will indicate whether these two columns are equal or not. My expected output is shown with the [Comparison] column:

 

However, when I use this measure

 

Comparison =
var A = SELECTEDVALUE('Table A'[Value_A])
var B = SELECTEDVALUE('Table B'[Value_B])
return IF(A == B, TRUE, FALSE)

I get this unintended result with many duplicated values:

 

Why is the row (C, C) not listed twice (I pointed this out above), and how do I obtain my intended result? Thanks for the help!

  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    3 years ago

    Hi, Anonymous 

    This occurs primarily because multi-ended tables cannot filter one-ended tables.

    Here are the steps you can refer to :

    (1)My test data is the same as yours.

    (2)We can create three measures :

    Value_A = var _aid= SELECTEDVALUE('Central'[ID_Table_A])
var _a_value=MAXX( FILTER(ALL('Table A'), 'Table A'[ID_Table_A] = _aid) ,[Value_A])
var _b = SELECTEDVALUE( 'Table B'[Value_B])
return
_a_value
    Value_B = var _bid= SELECTEDVALUE('Central'[ID_Table_B])
var _b_value=MAXX( FILTER(ALL('Table B'), 'Table B'[ID_Table_B] = _bid) ,[Value_B])
return
_b_value
    Comparison = IF( [Value_A]= [Value_B] ,"True","False")

     

    (3)Then we need to put the 'Central'[ID_Table_A] and 'Central'[ID_Table_B] on the visual , and the three measures then we will meet your need , the result is as follows:

    For multi-ended tables, if we need to get the contents of a one-ended table, but we don't want to create new columns to implement it, we need to put the ID of the multi-ended table in the visual.

     

    Best Regards,

    Aniya Zhang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

     

3 Replies

  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    Community Support
    3 years ago

    Hi, Anonymous 

    This occurs primarily because multi-ended tables cannot filter one-ended tables.

    Here are the steps you can refer to :

    (1)My test data is the same as yours.

    (2)We can create three measures :

    Value_A = var _aid= SELECTEDVALUE('Central'[ID_Table_A])
var _a_value=MAXX( FILTER(ALL('Table A'), 'Table A'[ID_Table_A] = _aid) ,[Value_A])
var _b = SELECTEDVALUE( 'Table B'[Value_B])
return
_a_value
    Value_B = var _bid= SELECTEDVALUE('Central'[ID_Table_B])
var _b_value=MAXX( FILTER(ALL('Table B'), 'Table B'[ID_Table_B] = _bid) ,[Value_B])
return
_b_value
    Comparison = IF( [Value_A]= [Value_B] ,"True","False")

     

    (3)Then we need to put the 'Central'[ID_Table_A] and 'Central'[ID_Table_B] on the visual , and the three measures then we will meet your need , the result is as follows:

    For multi-ended tables, if we need to get the contents of a one-ended table, but we don't want to create new columns to implement it, we need to put the ID of the multi-ended table in the visual.

     

    Best Regards,

    Aniya Zhang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Thank you, v-yueyunzh-msft! That solves the problem in my example.

     

    I will accept your answer as the solution, but I wanted to ask a followup question first. When I use your measures in my actual dataset (~3 gigabytes) and table visualization (~11 columns from ~5 tables), the table visualization times out after a long time. Do you have any advice? Could this be caused by blank values in any of the tables?

  • wongts_nms's avatar
    wongts_nms
    New Member
    1 year ago

    Hi, Expert,

    I also want 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 reference are:
    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 or column that matches Bank deposits to grouped GL entries based on total amount and reference number in Clean Ref Numeric.

    Created the below DAX but still unable to match:

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