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 ,

Use the DISTINCTCOUNT function.  I will demonstrate using 3 Measures:

1.  Calculate the unique number of Companies in your dataset:

Number of Companies = DISTINCTCOUNT( Table1[CompanyID])
   -- Using COUNTROWS instead should also give you the same result.

2.  Determine number of Companies that responded:

Number Companies Responded = DISTINCTCOUNT( Table2[CompanyID])

3. Then percentage responded:

%Response = DIVIDE( [Number Companies Responded],
                     [Number of Companies],
                   0 )

If you want, you can combine all of this into 1 Measure...I just split it so you can more easily see the method and know if your Measures are calculating accurately.

Hope this gets you going.

Regards,