Forum Discussion
need help!
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,
Hi, thanks!! I'm making progres, but Table 2 (survey results) contains companies that are not part of the key companies list in Table 1 (comprehensive list of key companies).
So the DISTINCTCOUNT measures are giving me full counts, which is great for Table 1 but for Table 2 the numerator needs to be only companies that are also found in Table 1. Thanks again, this is hugely appreciated!!
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,
Wow, I think that worked, thank you so much!!!!!