Show a count of related records in Power BI

In today’s post we’ll be looking at a Power BI topic! I’ll show you how you can add a measure to show a count of related records in a table related with the current record.

Scenario…

For the purposes of this post, lets look at a scenario to help this make sense. I’ve got 2 tables. One table is a store of document libraries or locations. The other table is a store of documents. I want to see on my table of document libraries, for each record, how many associated documents there are to each of those libraries.

That’s what we’re about to achieve!

Creating a measure

So, assuming you already have your two tables in Power BI and a relationship between them, so you will have a column in both tables where you’re matching GUIDs up whether your 1:N/N:1 relationship be in one direction or another.

I’m going to create a measure and call it ‘Number of documents’.

Now I’m going to create a variable and get the current record which once I put this in a visual with records will reference every record and make a measure against it producing another column.

So, I’m getting my current record with SELECTEDVALUE()

Within my SELECTEDVALUE() I’m going to use my document library id. So my parent record ID. So I need to store my current parent record ID. Remember my visual is going to be a list of parent records with an additional column of a count of child records.

Now, I’m going to add RETURN in my measure with the following on another line:

I’ll use CALCULATE() with a COUNT() of my documents using my document id field of my documents table. Then I’ll add a filter still within my CALCULATE() where I’m saying that in the document table, my document location column has to equal to my document variable (which is my current document location id).

Now I’m going to save the measure and add it to a visual with my document libraries. I’ll include the name field and a GUID so that I have something unique to prevent summarisation.

The code looks a bit like this…

No. of documents = 
VAR document = SELECTEDVALUE(documentlibrary[documentlibraryid])
RETURN
CALCULATE(COUNT(documents[documentid]),FILTER(documents,documents[documentlibrary] = document))

Now I’ll add this measure into my visual and as you can see I’ve got a list of my document libraries and how many document I have in each alongside them. (excuse my hidden document names 😉)

Hopefully this post helped you to get started with adding counts of related records to visuals of records in Power BI!