Hi Community!
I have client data set up similar to the table below - where the customers provide a value at each assessment. Technically, we administer these assessments at Initial (baseline), 30, 60, 90, 180 day intervals and a Final, however, sometimes the client doesn't provide a value.
Basically, I need to overlay filters over the measures I've created to look at the % change from baseline to 30-days, baseline to 60-days, baseline to final and so on - but I only want to include clients who have provided both a baseline rating and a 30-day rating or baseline and 60-day, etc.
Case A below, for example, should factor, via the filter created by way of a conditional column, into the % change on average from baseline to 60-days, but not baseline to 30-days - since Case A didn't provide a 30-day rating.
PWI Table
Case__c | Type | Value | Has Initial | Has 30-Day | Initial Value | 30 Day Value | 60 Day Value | Has Initial | Has 30 Day | Has 60 Day | Has Initial and 30 Day | Has Initial and 60 Day |
| A | Initial | 3 | Yes | No | 3 | | | | | | No | Yes |
| A | Final | 6 | No | No | | | | | | | No | Yes |
| A | Major Event | 7 | No | No | | | | | | | No | Yes |
| A | 60-Day | 6 | No | No | | | 6 | | | | No | Yes |
| A | 180-Day | 3 | No | No | | | | | | | No | Yes |
I'm sure there are far better ways to achieve, but I created the following conditional columns:
Step 1: To extract the value by assessment
Initial Value = IF(Table[Name] = "Initial", Table[Value], BLANK()
// This works and pulls the value into the row, only if the assessment is an Initial, so all good here.
Step 2:
Has Initial =
VAR CASE_NUM = PWI[Case__c]
VAR SEARCHVALUE = "Initial PWI"
VAR HAS_INITIAL = CALCULATE(
SELECTEDVALUE(PWI[PWI_Type__c], "No"),
FILTER(ALLNOBLANKROW(PWI[PWI_Type__c], PWI[Case__c]),
PWI[PWI_Type__c] == SEARCHVALUE && PWI[Case__c]= CASE_NUM),
ALL('PWI'))
RETURN
IF(HAS_INITIAL = "Initial PWI" && PWI[PWI INITIAL RATING] <> BLANK(), "YES", "NO")
// This appears to work and only notes "Yes" on the record for Customer A whose type is 'Initial' and the value is not blank.
Step 3:
HAS INITIAL PWI & PWI 60-DAY =
VAR CASE_NUM = PWI[Case__c]
VAR SEARCHVALUE = "YES"
VAR HAS_INITIAL = CALCULATE(
SELECTEDVALUE(PWI[HAS PWI INITAL], "No"),
FILTER(ALLNOBLANKROW(PWI[HAS PWI INITAL], PWI[Case__c]),
PWI[HAS PWI INITAL] == SEARCHVALUE && PWI[Case__c]= CASE_NUM),
ALL('PWI'))
VAR HAS_60 = CALCULATE(
SELECTEDVALUE(PWI[HAS 60-DAY PWI], "No"),
FILTER(ALLNOBLANKROW(PWI[HAS 60-DAY PWI], PWI[Case__c]),
PWI[HAS 60-DAY PWI] == SEARCHVALUE && PWI[Case__c]= CASE_NUM),
ALL('PWI'))
RETURN
IF(HAS_INITIAL = "YES" && HAS_60 = "YES", "YES", "NO")
// So, this works BUT the expression applies "YES" to each row for Client A --- and that's wherein my filter fails because if I throw this over the measure I've created for clients who have provided a baseline (Initial) and 60-Day Rating --- the filter is applying the "Yes" for every record including 'Major Event' a la the above table - which causes the averages to be off (oddly only the 60-day averages) and therefore, the % change.
How can I apply this expression to only the applicable records, so when looking at a % change from baseline to 60-days - only the clients who have provided both a baseline and 60-day rating are included and anyone whose only provided an initial rating, for example, are excluded?
I am stumped and would greatly appreciate any help!
