Response Rate Measure - Multiple Tables

[ntec35]

I have two tables, one with total survey recipients and one with survey responders. I want to be able to apply filters to show response rate by state and by an executive (Y/N) column.  I created a measure to calculate response rate which works correctly until I try to apply the filters, it looks like the measure is still using the total survey recipients as the denominator even after the filters should be applied.  Is there a different way I can structure the data to fix this problem?  The State and Executive columns are both many to many relationships, I tried adding relationships but the calculation doesn't work across both filters whether I create a relationship or not.  

Response Rate = Divide(count('Master Responses List'[Email Address]),COUNT('Master Recipient List'[Email]))

Master Responses List

State	Email Address	Executive
Mississippi	example@example.com	No
Pennsylvania	pa@pa.com	Yes

Master Recipients List

State	Email Address	Executive
Mississippi	example@example.com	No
Pennsylvania	pa@pa.com	Yes
Mississippi	hello@hello.com	No

[v-luwang-msft]

Hi @ntec35 ,

Has your problem been solved, if so, please consider Accept a correct reply as the solution or share your own solution to help others find it.

Best Regards
Lucien

[TomMartens · Accepted solution]

Hey @ntec35 ,

without knowing your data model or report it's really difficult to recommend a different approach.

My assumption is as follows, in your report you columns from the Response table to filter this table down, as both are not related the Recipients table will not be filtered, this then explains the "total" denominator.

Create "dimension" tables like "State" and "Executive". Create relationships between these dimension tables (the one-side) and your existing tables (the many-side). Use columns from these dimension tables in your slicers and visuals.
This provides a good introduction to data modeling: Model data in Power BI - Learn | Microsoft Docs 

Maybe you also have to change the measure if necessary like so, if you want to avoid an executive yes/no table: 

DIVIDE(

CALCULATE(
COUNT( respons[email] )
, response[executive] = "YES"
)
,
CALCULATE(
COUNT( recipient[email] )
, recipient[executive] = "YES"
)

)

Hopefully, this provides what you need to tackle your challenge.

Regards,

Tom