Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

need help!

Hello, I have 2 source tables.  Table 1 is a comprehensive list of companies (each record is a company), and Table 2 is a list of individuals, their companies, and survey results for that individual ...
Need Help
  • rsbin's avatar
    rsbin
    2 years ago

    Anonymous ,

    Suggest you create a new Calculated Column in Table 2 (your Survey table):

     

    KeyCompany = RELATED( DIM_Company[CompanyID] )

     

    My table DIM_Company is your Table 1 (Master Key Companies)

    If the Company ID is in that table, it will appear, else will appear as Blank.

     

    Then do a DISTINCTCount on this new column.  This will exclude unwanted companies.

    KeyCompanies = Calculate( DISTINCTCOUNT( Surveys[KeyCompany] ),
                  FILTER( Surveys, Surveys[KeyCompany] <> BLANK() ))

    Hope this works for you.

    Regards,

rsbin's avatar
rsbin
Community Champion
2 years ago

Anonymous ,

Suggest you create a new Calculated Column in Table 2 (your Survey table):

 

KeyCompany = RELATED( DIM_Company[CompanyID] )

 

My table DIM_Company is your Table 1 (Master Key Companies)

If the Company ID is in that table, it will appear, else will appear as Blank.

 

Then do a DISTINCTCount on this new column.  This will exclude unwanted companies.

KeyCompanies = Calculate( DISTINCTCOUNT( Surveys[KeyCompany] ),
                  FILTER( Surveys, Surveys[KeyCompany] <> BLANK() ))

Hope this works for you.

Regards,

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Wow, I think that worked, thank you so much!!!!!