Checking 2 Tables to Identify Identical Values

[markefrody]

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. Identical values should be marked as "Yes", if not identical it should be "No".

1. Here is an example. Below are 2 tables:

2. In Table 1, I need to identify which Container # are identical to Table 2. Create a new column to mark which container numbers are identical ("True" if identical, "False" if not identical).

Do I need to creat a relationship for these 2 tables before creating a DAX? Any assistance that you can provide would be greatly appreciated. 

Best regards,
Mark V.

[PaulDBrown · Accepted solution]

@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" )

[markefrody]

Hi @Vera_33 and @Ashish_Mathur,

Thank you for responding to my post. I tried your solutions but DAX won't let me add the field name of the column for Table 2.  I have created the calculated column formula in Table 1.

It appears it won't allow me to use FILTER, EARLIER, CALCULATE, and COUNTROWS

Here are the table names and column names to be precise:
Table 1 name:  container_manager_project_container_count
Table 1 column name: container_manager_project_container_count [Project # (Text)]

Table 2 name: container_manager_project_countainer_count_noDO
Table 2 column name:  container_manager_project_countainer_count_noDO [Project # (Text)]

I forgot to indicate that:
1. I am using Storage Mode as "Direct Query". 
2. Table 2 is a duplicated table of Table 1 but I made some filters in Power Query for specific fields in Table 2. So technically they are not similar anymore.

Please let me know if there is a workaround for this. Thanks.

[v-shex-msft]

Hi @markefrody,

I think this is caused by the 'direct query' connection mode, it has limited the Dax function usage when you are trying to create a calculated column/table in direct query mode.

Use DirectQuery in Power BI Desktop 

Regards,

Xiaoxin Sheng

[Vera_33]

Hi @markefrody 

You are missing one closing ")" , and use variable instead of EARLIER should be more readable and intuitive

compare =

IF(

    CALCULATE(COUNTROWS(table),

            FILTER(table1, [column]=EARLIER([another column]))>0,"Yes","No"

    )

)

[Vera_33]

Hi @markefrody 

Do you have duplicated Container numbers? Do you need to consider the date? Here is a simple one without considering duplicates or date, no relationship:

Cal Column = Not( ISBLANK(LOOKUPVALUE(Table2[Container #],Table2[Container #],Table1[Container #])))

[Ashish_Mathur]

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")