I have one data table from which I have to calculate 2 different KPIs, each KPI is tied to different date column.
"Creation Date" for KPI "Net Satisfaction Score" calculation and "Uni Date" for KPI "Response Rate" calculation.
"Date" from "Date table" used as field to filter time periods so I need to have a relation to that field.
If I filter for results in September'22, I want to see "Net Satisfaction Score" calculated from all Ids with Creation date in SEP'22, and I want to see "Response Rate" calculated from all Ids with UniDate in SEP'22 (this means Ids 00004, 00007, 00009 and 00010 are not to be considered in Response Rate calculation).
What I have tried already:
Using more queries - one for Response Rate (with relation UniDate <-> Date) and second one for Net Satisfaction Score (with relation Creation Date <-> Date).
This worked, but if I want to go more into detail and see the results by country, the numbers don't show up correctly, as there is no relation on "Country" or whatever detail I want to split the result by.
Making relation based on IDs between the queries mentioned in "1)" - circular dependency error.
I am really out of ideas, but maybe some of you tried to solve this kind of issue already.
Sounds like you need to use USERELATIONSHIP within your measure. You will need something like this
Net Satisfaction Score = CALCULATE(Sum('Net Satisfaction Table[Score]'),
USERELATIONSHIP('DateTable[Day]','Net Satisfaction Score Table[Created Date]))
You can obviously use sum, count, average...whatever you need to do with your score. You also need to make sure that both dates - Created Date and UniDate have an inactive relationship back to your Date Table. Repeat the same measure example for your other measure. USERELATIONSHIP works perfectly on inactive relationships and only works within CALCULATE operation where you tell PowerBI which date to use in this calculation.