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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
lugeno
New Member

Count records with related records

Hello!

 

I'm new to Power BI.

I'm gong to describe my situation (I connected the software to the webapi uri 8.2 of my CRM online):

 

I have Entity A (with a text field "name")

I have Entity B related to A (N:1)

I have Entity C related to A (N:1)

 

I need to extract the number of records of A with "name" not null, that have at least one record of B AND at least one record of C.

I have no idea of the steps to fulfill my need.

 

Thanks in advance for the help!

 

Best regards 

1 ACCEPTED SOLUTION
v-huizhn-msft
Microsoft Employee
Microsoft Employee

Hi @lugeno,

In table A, please create two calculated columns to get count of records B and C using the formula. Lets assume the column name used to build relationship is "field".

CountB = CALCULATE(COUNT(TableB[record),USERELATIONSHIP(TableA[field],TableB[field]))
CountC = CALCULATE(COUNT(TableC[record),USERELATIONSHIP(TableA[field],TableC[field]))

 

Then you can create a measure to get the the number of records of A with "name" not null, that have at least one record of B AND at least one record of C. Please review the formula below.

number=CALCULATE(COUNT(TableA[name]),FILTER(TableA,TableA[name]<>BLANK()&&TableA[CountB])>=1&&TableA[CountC])>=1))


You can create a card visual to display your result.

Best Regards,
Angelia

 

View solution in original post

1 REPLY 1
v-huizhn-msft
Microsoft Employee
Microsoft Employee

Hi @lugeno,

In table A, please create two calculated columns to get count of records B and C using the formula. Lets assume the column name used to build relationship is "field".

CountB = CALCULATE(COUNT(TableB[record),USERELATIONSHIP(TableA[field],TableB[field]))
CountC = CALCULATE(COUNT(TableC[record),USERELATIONSHIP(TableA[field],TableC[field]))

 

Then you can create a measure to get the the number of records of A with "name" not null, that have at least one record of B AND at least one record of C. Please review the formula below.

number=CALCULATE(COUNT(TableA[name]),FILTER(TableA,TableA[name]<>BLANK()&&TableA[CountB])>=1&&TableA[CountC])>=1))


You can create a card visual to display your result.

Best Regards,
Angelia

 

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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