Need to count unique values from 7 different tables in dynamic date range

[DimaMD]

Need help,

I need to count unique companies from 7 different tables.

Each table has ID of company, dates on which records were created. Tables are connected with each other with calendar. The tas is to count summary of unique company records, that are connected to this records.

For example:

Table 1:

accountid: x

Createdon: 23.09.2021

createdby: Arnold

Table 2:

accountid: x

Createdon: 22.09.2021

createdby: Arnold

Table 3:

accountid: y

Createdon: 21.09.2021

createdby: Sara

Here we can see that we have 3 recrods in 3 different tables, that was created by 2 different users. but 2 of those tables have the same companyid, and the 3d table has different companyid, so in result we need to get 2 unique companies (account) created by 2 different users. 

Also we need to consider dates in calendar, so we could see how many unique companies were involved in different period of time (dates) by filtering dates "createdon" and users that created records

Original task considers 7 different tables, where we need to count companies.

Will be very grateful for help.

[Greg_Deckler · Accepted solution]

@DimaMD Maybe:

Measure =
  COUNTROWS(
    DISTINCT(
      UNION(
        SELECTCOLUMNS('Table 1',[accountid]),
        SELECTCOLUMNS('Table 2',[accountid]),
        SELECTCOLUMNS('Table 3',[accountid]),
        SELECTCOLUMNS('Table 4',[accountid]),
        SELECTCOLUMNS('Table 5',[accountid]),
        SELECTCOLUMNS('Table 6',[accountid]),
        SELECTCOLUMNS('Table 7',[accountid])
      )
    )
  )

[DimaMD]

Thank you very much👍