Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
markefrody
Post Patron
Post Patron

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:

markefrody_0-1612915980989.png

 

markefrody_1-1612916032914.png


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

markefrody_2-1612916168854.png


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.


 

 

1 ACCEPTED SOLUTION
PaulDBrown
Community Champion
Community Champion

@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.

result.JPG

 

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

 

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

6 REPLIES 6
PaulDBrown
Community Champion
Community Champion

@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.

result.JPG

 

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

 

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






markefrody
Post Patron
Post Patron

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
Capture 2.PNG
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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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
Resident Rockstar
Resident Rockstar

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
Super User
Super User

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors