Count all records from another table

[Anonymous]

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. 

[Syndicate_Admin]

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.

[Greg_Deckler]

@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. 

[Anonymous]

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

[Anonymous]

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. 

https://unsw-my.sharepoint.com/:u:/g/personal/z3521828_ad_unsw_edu_au/EXByMUKmgOxLqeLUERK5IwIByj0tIz... 

[nandukrishnavs]

@Anonymous  If you disable the relationship, it will work. Do you really need relationship between these two tables?

[Anonymous]

thanks @nandukrishnavs  I have tried the calculation with no relationships between the tables. Unfortunately its not working. 

[Greg_Deckler]

Headcount =

COUNTROWS(

FILTER(ALL('Table 2'), 'Table 2'[Code] = MAX('Table 1'[Code])

))

PBIX is attached.