Forum Discussion
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. 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.
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" )
6 Replies
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,
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")
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 #])))
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
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"))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
