Forum Discussion
Checking 2 Tables to Identify Identical Values
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" )
Hi Anonymous 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.
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