Forum Discussion

kens8's avatar
kens8
Frequent Visitor
3 years ago
Solved

Unmatching elements from 2 columns from 2 different tables

Hello, this is the situation.

I have 2 tables that I linked on the column that that (normally) contain unique elements. It is supposed to be the sama in both tables but in either tables, there are elements that exist in one table and do not exist in another table and vice versa. It makes that there are matching elements and unmatching elements.

I want to obtain the un matching elements from both columns of both tables.

 

To let you understand : For example

TABLE 1                                    TABLE 2

1                                                  4

2                                                  5

3                                                  6

4                                                  7

5                                                  8

 

I am looking for the unmatching elements from either columns of each table. That is,

Table 1 : 1, 2, 3

Table 2 : 6, 7, 8

I do not really need 4 and 5 since they are matching. Having them would be a plus.

 

This is what I did :

I linked either columns from both table and it gave me a (many-to-many relationship) and I don't understand why since they are normally unique elements.

Then, I tried the RELATE function, it didn't work. It said it can't find the column.

Now, I don't know what to do.

 

Can you hepl me please solve this problem ?

 

Thank you.

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi  kens8 ,

     

    Here are the steps you can follow：

    1. Create calculated column.

    Table =
var _column1=
SELECTCOLUMNS('Table2',"table2",[Flag])
var _column2=
SELECTCOLUMNS('Table1',"table1",[Flag])
var _table1=SELECTCOLUMNS(
    FILTER(ALL('Table1'),
    NOT('Table1'[Flag]) in _column1),"flag",'Table1'[Flag])
var _table2=
SELECTCOLUMNS(
    FILTER(ALL('Table2'),
    NOT('Table2'[Flag]) in _column2),"flag",'Table2'[Flag])
return
UNION(
    _table1,_table2)

    2. Result:

     

    Best Regards,

    Liu Yang

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

3 Replies

  • smpa01's avatar
    smpa01
    Icon for Community Champion rankCommunity Champion
    3 years ago

    you can run this on t1

     

    Measure = IF(ISEMPTY(RELATEDTABLE(t2)), MAX(t1[t1]))

     and the opposite in t2

     

     

    • kens8's avatar
      kens8
      Frequent Visitor
      3 years ago

      Hello, I tried but it gives empty tables.

      Note that in the corresponding columns, the format is "text" as it contains both figures and letters and I guess that's why the "MAX" function is not working.

      May be an alternative to the MAX function ?

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi  kens8 ,

     

    Here are the steps you can follow：

    1. Create calculated column.

    Table =
var _column1=
SELECTCOLUMNS('Table2',"table2",[Flag])
var _column2=
SELECTCOLUMNS('Table1',"table1",[Flag])
var _table1=SELECTCOLUMNS(
    FILTER(ALL('Table1'),
    NOT('Table1'[Flag]) in _column1),"flag",'Table1'[Flag])
var _table2=
SELECTCOLUMNS(
    FILTER(ALL('Table2'),
    NOT('Table2'[Flag]) in _column2),"flag",'Table2'[Flag])
return
UNION(
    _table1,_table2)

    2. Result:

     

    Best Regards,

    Liu Yang

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