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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
goalie_
Helper III
Helper III

DAX measure for counting values between two columns

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 ))))
1 ACCEPTED SOLUTION
harshnathani
Community Champion
Community Champion

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)

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

 

// 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
Community Champion
Community Champion

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
Community Champion
Community Champion

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)

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.