Count unique rows based on some parameter with DAX

Hi all members,

I need help in creating a measure to count the number of Message_id by users under each department separately. I also want to show the data with heatmap custom visual. I have two tables with the following details:

  1. Messages: Message_id, User_id

  2. Users: User_id, User_name, Department

The two tables are linked by User_id. The Message_id has unique values so the measure can be a COUNTROWS function. Looking forward to your suggestions!

Hi there,

You could try using this formula for each department:

Department 1 = CALCULATE([Count of messages],FILTER(Users,Users[Department]="Department 1"))

You could also use COUNTROWS within the CALCULATE statement here as well.

Check out some examples here in the mastering DAX course at Enterprise DNA Online.

I hope this answers your query. Let me know if you have any clarification.

Here’s some ideas around using FILTER with CALCULATE. Check out these links below