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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
IvanMislav
Helper I
Helper I

COUNTAX with related tables

Here is the situation. I have two tables: "Messages" and "Users" which are related throught User_id.

 

Mesagges: Message_id, User_id

Users: User_id, User_name, Department

 

What i want to do is to create a measure (messagesMarketing) which will count the number of Message_id (it can be COUNTROWS function since message_id are unique values) filtered by users in Department = "Marketing".

 

Can anyone help me with this, please?

 

1 ACCEPTED SOLUTION

Hey, I realised my formula was incorrect as I had missed out the parenthesis for the countrows portion. It should have read:

 

=CALCULATE(COUNTROWS(messageid), filter(deparment)="department1")

=CALCULATE(COUNTROWS(messageid), filter(deparment)="department2")

...

=CALCULATE(COUNTROWS(messageid), filter(deparment)="department n")

// if this is a solution please mark as such. Kudos always appreciated.

View solution in original post

5 REPLIES 5
samdthompson
Memorable Member
Memorable Member

Hi. The way you describe the data it sounds like its already got the relationship. If thats not the case then make the relationship - in relationship view drag Messages[user_id] onto User[user_id]

 

Then on the messages table write the measure. messagesMarketing=countrows(message_id)

 

Then in report builder drag your measure on to the canvas and your marketing department onto the canvas (make slicer). The slicer will now return the countrows of messages for any selected department.

 

 

 

 

 

//if this is a solution to your problem please mark as such

// if this is a solution please mark as such. Kudos always appreciated.

Thx for the answer but it's not what I need. I need to create measure for each department separately because i want to show the data with Heatmap custom visual, and it cannot be done the same way as, for example Matrix visual. 

ok what about:

 

=CALCULATE(COUNTROWS(messageid, filter(deparment)="department1")

=CALCULATE(COUNTROWS(messageid, filter(deparment)="department2")

...

=CALCULATE(COUNTROWS(messageid, filter(deparment)="department n")

 

 

// if this is a solution please mark as such. Kudos always appreciated.

Thanks Sam, 

 

I finally figured it out. I just needed to slightly modify your suggestion.

 

When i tried...

 

= CALCULATE(COUNTROWS('Messages', FILTER(Users,Users[Department]="department1")))

 

This gives me an error saying "Too many arguments were passed to the COUNTROWS function". So i made a separate measure Count of messages= COUNTROWS('Messages') and then i used that mesure for my formula.

 

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

 

Now it works like a charm

Hey, I realised my formula was incorrect as I had missed out the parenthesis for the countrows portion. It should have read:

 

=CALCULATE(COUNTROWS(messageid), filter(deparment)="department1")

=CALCULATE(COUNTROWS(messageid), filter(deparment)="department2")

...

=CALCULATE(COUNTROWS(messageid), filter(deparment)="department n")

// if this is a solution please mark as such. Kudos always appreciated.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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

Top Solution Authors