Forum Discussion
DAX Count Based on 2 Name (Key) Columns
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!
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]) )
2 Replies
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]) )
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!!
