Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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]))
State | Email Address | Executive |
Mississippi | example@example.com | No |
Pennsylvania | pa@pa.com | Yes |
State | Email Address | Executive |
Mississippi | example@example.com | No |
Pennsylvania | pa@pa.com | Yes |
Mississippi | hello@hello.com | No |
Solved! Go to 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
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
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
User | Count |
---|---|
96 | |
87 | |
78 | |
72 | |
69 |
User | Count |
---|---|
113 | |
105 | |
84 | |
65 | |
64 |