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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Koushikrish
Helper I
Helper I

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. 

 

Koushikrish_0-1657755876169.png

 

(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:

 

Koushikrish_1-1657755977839.png

 

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!

 

 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@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...

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@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...

@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!!

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

Top Solution Authors