Forum Discussion
Count Linked Records When an Intermediary Table Creates the Relationship Between Two Tables
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.
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.
2 Replies
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?
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.