Forum Discussion

goalie_'s avatar
goalie_
Icon for Helper III rankHelper III
6 years ago
Solved

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 ))))
  • Anonymous's avatar
    Anonymous
    6 years ago

    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)

3 Replies