DAX Count Based on 2 Name (Key) Columns

[Koushikrish]

Hello,

I am working on a Kudos report for my team's dashboard. The dataset I have kind of looks like this with just the 3 columns. 

(The Recepient is the person who was rewarded a kudos point while Nominator is the own who nominated that person.)

For the dashboard, I need a view like this based on each user:

This is for us to know how many times a user who nominated another user had actually received a kudos point.

How can I create these 2 measures 'Times Received' and 'Times Nominated' effectively in DAX? This is basically just a Count but as the Recepient and Nominator are 2 seperate columns, do I need to create another calculated column per each row to identify the value of these 2 measures? Or can it be done via a calculated table or a simple measure? 

Would appreciate any help on this. Please let me know if you need more information!

[amitchandak · Accepted solution]

@Koushikrish , You need to have a username table, if you do have to create one

username = distinct(union(distinct(Table1[Recepient]),distinct(Table2[Nominator])))

Join with both tables. One join will be inactive.

You just need a count/distintcount measure.

For inactive use userelationship to activate relation

Count(Table[ID]) //active join

Assume Nominator is inactive

calculate(Count(Table[ID])  ,userelationship (username[Recepient] , Table[Nominator]) )

https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in...

[Koushikrish]

@amitchandak : That worked as I expected. Also there was only one table and not multiple so I used the 2 different columns (Recipient and Nominator). Thanks for your help!!