Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Comparing the two tables

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 KeyExisting in Table1Existing in table2Columns Not MatchMatch%
11YesYes1050%
12YesNo200%
13NoYes200% 
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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. 

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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. 

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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