How to return the count of something related in another table?

[dinosainsburys]

Hi all,

I'm trying to return the count of something related in another table.

So I have two tables. One named 'Business Services' and another named 'Monitors'.

Business Service is a table containing business services which its name column is all unique and no duplicates.

Monitors is a table with unique names for each monitor, but can relate many times to the same business service. As a result, there is a many to one relationship (cross filter: both) from monitors to business services.

What I want is to display the count of monitors against a business service and have this column within my business service table but I have been unable to. I have been trying to use DAX functions like SELECTCOLUMNS etc but they all want a single value returned. Any ideas? 

[Ashish_Mathur]

Hi,

Assuming you want to do this via a measure, try this.  Drag the column from the Business Services table to the Table visual and write this measure

Measure = countrows(Monitors)

Hope this helps.

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure if I understood your question correctly, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

One of ways is to use RELATEDTABLE DAX function in the Calculated Column.

RELATEDTABLE function (DAX) - DAX | Microsoft Learn

monitor count CC = 
COUNTROWS(RELATEDTABLE(monitor))