Comparing the two tables

[Anonymous]

I would like to compare the columns between two tables  example all the values from  table1 to all all the values in table2 based on unique value. Considering 20 Columns in the table.

How do we create a DAX formula for this?

Primary Key	Existing in Table1	Existing in table2	Columns Not Match	Match%
11	Yes	Yes	10	50%
12	Yes	No	20	0%
13	No	Yes	20	0%

[v-rzhou-msft]

Hi @Anonymous 

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.

Best Regards,

Rico Zhou

[v-rzhou-msft · Accepted solution]

Hi @Anonymous 

Due to I don't know your data model, I build two sample tables to have a test.

Table1:

Table2:

Build a Union table by Dax.

UnionTable = SUMMARIZE(UNION(Table1,Table2),[Primary Key],[Value])

Table1Match = 
VAR _Value = CALCULATE(SUM(Table1[Value]),FILTER(Table1,Table1[Primary Key]=EARLIER(UnionTable[Primary Key])&&Table1[Value]=EARLIER(UnionTable[Value])))
Return
IF(ISBLANK(_Value),0,1)

Table2Match = 
VAR _Value = CALCULATE(SUM(Table2[Value]),FILTER(Table2,Table2[Primary Key]=EARLIER(UnionTable[Primary Key])&&Table2[Value]=EARLIER(UnionTable[Value])))
Return
IF(ISBLANK(_Value),0,1)

Then let's build a compare table.

Compare Table = 
ADDCOLUMNS (
    GENERATESERIES ( 11, 13, 1 ),
    "Existing in Table1", IF ( [Value] IN VALUES ( Table1[Primary Key] ), "Yes", "No" ),
    "Existing in Table2", IF ( [Value] IN VALUES ( Table2[Primary Key] ), "Yes", "No" )
)

Columns Not Match = 
VAR _Match =
    COUNTAX (
        FILTER (
            UnionTable,
            UnionTable[Table1Match] = 1
                && UnionTable[Table2Match] = 1
                && UnionTable[Primary Key] = EARLIER ( 'Compare Table'[Primary Key] )
        ),
        UnionTable[Primary Key]
    )
VAR _all =
    IF (
        'Compare Table'[Existing in Table1] = "Yes",
        COUNTAX (
            FILTER (
                Table1,
                Table1[Primary Key] = EARLIER ( 'Compare Table'[Primary Key] )
            ),
            Table1[Primary Key]
        ),
        COUNTAX (
            FILTER (
                Table2,
                Table2[Primary Key] = EARLIER ( 'Compare Table'[Primary Key] )
            ),
            Table2[Primary Key]
        )
    )
RETURN
    _all - _Match

Match% = 
VAR _Match =
    COUNTAX (
        FILTER (
            UnionTable,
            UnionTable[Table1Match] = 1
                && UnionTable[Table2Match] = 1
                && UnionTable[Primary Key] = EARLIER ( 'Compare Table'[Primary Key] )
        ),
        UnionTable[Primary Key]
    )
VAR _all =
    IF (
        'Compare Table'[Existing in Table1] = "Yes",
        COUNTAX (
            FILTER (
                Table1,
                Table1[Primary Key] = EARLIER ( 'Compare Table'[Primary Key] )
            ),
            Table1[Primary Key]
        ),
        COUNTAX (
            FILTER (
                Table2,
                Table2[Primary Key] = EARLIER ( 'Compare Table'[Primary Key] )
            ),
            Table2[Primary Key]
        )
    )
VAR _Result = _Match/_all
RETURN
IF(_Result = BLANK(),0,_Result)

Result is as below.

You can download the pbix file from this link: Sample

Best Regards,

Rico Zhou

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

[v-rzhou-msft]

Hi @Anonymous 

Could you show me a sample like the two tables you are dealing with?

Or you can share your pbix file with me by your Onedrive for Business.

This will make it easier for me to understand your requirement.

Best Regards,

Rico Zhou

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