Count records with related records

[lugeno]

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 

[v-huizhn-msft · Accepted solution]

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