Count Linked Records When an Intermediary Table Creates the Relationship Between Two Tables

[MTOnet]

I have data in 3 tables.  I am trying to count how many records are in the third table, which are related to items in the first table.  There is an intermediary table between the two that establishes the relationship.

Sample Data

Table A

RecordA	LinkedRecordB
123	210
124
125	211
126	212
127

Table B

RecordB	LinkedRecordC
210	380
211
212	381

Table C

RecordC

380

381

383

In this example, there would be items in Table C, linked to Table A.  

[Anonymous · Accepted solution]

Hi  @MTOnet ,

Here are the steps you can follow：

1. Connect the relationship between the three tables

2. Create measure

count =
CALCULATE(COUNT(TableC[RecordC]),FILTER(ALL(TableA),[LinkedRecordB]<>BLANK()))

3. Result.

You can downloaded PBIX file from here.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @MTOnet 

What is the expected result? Is Record A distinct in Table A, and Record B distinct in Table B? And you want a DAX calculated column in Table C?