Forum Discussion

markefrody's avatar
markefrody
Post Patron
5 years ago
Solved

Checking 2 Tables to Identify Identical Values

Hi  Sir and Ma'am,   I'm looking for a DAX to accomplish the following: I have 2 tables wherein there are identical values. I need to have table 1 to reflect which values are identical to table 2....
Need Help
  • PaulDBrown's avatar
    PaulDBrown
    5 years ago

    markefrody 

    Here is one way. Create a measure as follows

     

    Claimed =
VAR Identical =
    COUNTROWS ( INTERSECT ( Table1, Table2 ) )
RETURN
    IF ( Identical = 1, "Yes", "No" )

     

    Now create a table visual from table1 and add the measure.

     

    Edit: if the names of the columns are different, the you might need to use the following (which renames the columns to match them):

     

    Claimed =
VAR T1 =
    SELECTCOLUMNS ( Table1, "ID", Table1[ID], "Date Shipped", Table1[Date shipped] )
VAR T2 =
    SELECTCOLUMNS ( Table2, "ID", Table2[ID], "Date Shipped", Table2[Date shipped] )
VAR identical =
    COUNTROWS ( INTERSECT ( T1, T2 ) )
RETURN
    IF ( identical = 1, "Yes", "No" )

     

     

Ashish_Mathur's avatar
Ashish_Mathur
Super User
5 years ago

Hi,

Write this calculated column formula in Table 1.  No relationship between the 2 tables is needed.

=if(calculate(countrows('table 1'),filter('table 2','table 2'[container #]=earlier('table 1'[container #])&&'table 2'[date shipped]=earlier('table 1'[date shipped]))>0,"Yes","No")