Joining summarizes from 2 different tables.

[bolabuga]

Hello everyone,

I have 2 tables and both have client numbers. 

Table1 are much larger and is for normal clients.

Table2 have only special clients.

I need a third table with just the client numbers from the 2 tables, containing only distinct client numbers. Its possible have duplicities of client numbers on Table1 and Table2, because a existing normal client can be upgrade to special client anytime, or demoted from special to normal.

I would like to have a join of those 2 summarizes, resulting in a Table3 with distinct values of all client codes. 

Summarize ( Table1 ; Table1[client code] )

Summarize ( Table2 ; Table2[client code] )

[v-yulgu-msft]

Hi @bolabuga,

Please try this:

Table 3 = SUMMARIZE(UNION( 'Table 1','Table 2'),[client code])

Best regards,

Yuliana Gu

[MFelix]

Hi @bolabuga,

Create a calculate table using the following code:

UniqueClientID = UNION(DISTINCT(Table1[Client Code]);DISTINCT(Table2[Client Code]))

Regards,

MFelix

[bolabuga · Accepted solution]

@MFelix

UniqueClientID = UNION(DISTINCT(Table1[Client Code]);DISTINCT(Table2[Client Code]))

This way its still bringing a new table where i have "duplicities".

Distinct table1[client] = only distinct clients from table1

Distinct table2[client] = only distinct clients from table2

In here, considering those 2 distinct tables, they still have duplicities between them, and excuting the union is bringing more or less 30 clients with 2 ocurrences on the "uniqueclientID" table.

@v-yulgu-msft

Table 3 = SUMMARIZE(UNION( 'Table 1','Table 2'),[client code])

I dont know if im making something wrong trying to write this code, but when writing [client code], its forcing me to select the client code from the Table1. Trying to run this code is also giving me a error, i will try to translate from ptbr here, "all the arguments in the UNION table should have the same number of columns.

I greatful to you both, analysing the 2 suggestion helped me to understand better what i needed and helped to write something that worked.

Table3 = 
DISTINCT(
    UNION(
        SUMMARIZE(Table1;Table1[client code]);
        SUMMARIZE(Table2;Table2[client code])))