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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.