topic Re: DAX measure for counting values between two columns in DAX Commands and Tips

DAX measure for counting values between two columns

goalie_ — Thu, 25 Jun 2020 17:42:24 GMT

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

Re: DAX measure for counting values between two columns

Anonymous — Thu, 25 Jun 2020 18:03:17 GMT

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)

Re: DAX measure for counting values between two columns

Anonymous — Thu, 25 Jun 2020 18:17:33 GMT

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/1182531#M531771

Regards,

Harsh Nathani

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

Re: DAX measure for counting values between two columns

Anonymous — Mon, 29 Jun 2020 11:40:51 GMT

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