DAX measure for counting values between two columns

[goalie_]

I have two columns, each from a different table that are linked. I want to find the count of how many distinct and similar values are between the columns. 

For example if one table had a column with values: [chicken, fish, rice, tree, car] and the other table's column had the values [fish, boat, rice, rice, ears, mouse], I would want the count to be 2 because rice and fish show up in both columns.

Edit: I was thinking something along the lines of a distinctcount and an innerjoin. I can't get it to work

COUNTROWS(NATURALINNERJOIN(SELECTCOLUMNS(column from table 1 ), SELECTCOLUMNS( column from table 2 ))))

[Anonymous]

// Given: T1 and T2 that are somehow linked
// meaning a filter can be applied to both
// tables at the same time. We're interested
// in T1[col] and T2[col].

[# Same Values] =
	COUNTROWS(
		INTERSECT(
			VALUES( T1[col] ),
			VALUES(	T2[col] )
		)
	)

Best

D

[harshnathani]

Hi @goalie_ ,

You can also have a look at this article

https://www.sqlbi.com/articles/from-sql-to-dax-joining-tables/

https://community.powerbi.com/t5/Desktop/DistinctCount-for-matching-values-on-two-columns/m-p/118253...

Regards,

Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[harshnathani · Accepted solution]

Hi @goalie_ ,

You can try a measure

CALCULATE(DISTINCTCOUNT(Table1[Column1]),FILTER(CROSSJOIN(Table1,Table2), Table1[Column1] = Table2[Column1]))

Regards,

Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)