Forum Discussion
Count all records from another table
Hi All,
I have not found a way to do this at all yet, help would be appreciated. It should be relatively straightforward. I have a primary table of submitted records and a larger table with all non-submitted and submitted records. I need to be able to calculate headcount as a count of all non-submitted and submitted records.
I have two tables:
Table 1 is the primary table and all statistics on submitted records are calculated. This table is only submitted records so as to maximise performance of the report and graphs. Most pages in the report uses Table 1 only.
| TimeFrame | Code | Slicer | ID | User | Status |
| 1 2020 | Course1 | 3 | ID2 | User 2 | Submitted |
| 1 2020 | Course1 | 4 | ID3 | User 3 | Submitted |
| 3 2019 | Course2 | 4 | ID4 | User 4 | Submitted |
Table 2 has all records, both submitted and not submitted.
| TimeFrame | Code | Slicer | ID | User | Status |
| 1 2020 | Course1 | 3 | ID1 | User 1 | DNR |
| 1 2020 | Course1 | 3 | ID2 | User 2 | Submitted |
| 1 2020 | Course1 | 4 | ID3 | User 3 | Submitted |
| 3 2019 | Course2 | 4 | ID4 | User 4 | Submitted |
| 4 2019 | Course2 | 5 | ID5 | User 5 | DNR |
| 5 2019 | Course2 | 5 | ID6 | User 6 | DNR |
Expected result:
For all courses in Table 1 I would like to be able to calculate the total number of distinct users in table 2.
The headcount I would expect for Course 1 is 3.
I would like a dynamic measure so I can aggregate above the course level. This is why I have not used Summarized tables or calculated columns if I can avoid them.
I have spent a few hours on this and while I can calculate it from Table 2 easily, it breaks when I try to use it with relationships.
Help would be greatly appreciated.
7 Replies
- Greg_Deckler
Community Champion
Anonymous - First, you mention Course 1 but I do not see a course 1 in your data. Where you referring to Course 13 and there should be 2 users, one that registered and one that did not?
How are the tables related to one another? Or how are you attempting to relate the tables to one another?
You could just leave them unrelated to one another and do something like:
Measure =
COUNTROWS(
FILTER('Table2', 'Table2'[Course] = MAX('Table1'[Course]) && 'Table2'[TimeFrameCode] = MAX('Table1'[TimeFrameCode]))
)
Something along those lines for example.
Thanks so much Greg_Deckler
There is a course1 there 3 is in the slicer column. I cant seem to get the tables clearer than that.
The tables are currently linked by ID bi directional.
I'll try the formula shortly - frozen screen 😞 at the moment
Hi Greg_Deckler ,
Cant seem to get it working. I have created a pbix file that illustrates my particular issue. I have not linked the tables together in this.
Syndicate_AdminAdministrator
Good morning, I have a table that has the detail of all the invoices of all the suppliers (Table invoices) and another that has the detail some of the proeveedores, I would like to know how I do to count the invoices, but only of the suppliers that coincide with those of the table of selected suppliers.
Agrdezco of antemmano the help.
