cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
samdep
Helper III
Helper III

DAX for Customer with Initial Rating and 60-Day Rating - Stumped!

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

TypeValueHas InitialHas 30-DayInitial Value30 Day Value60 Day ValueHas InitialHas 30 DayHas 60 DayHas Initial and 30 DayHas Initial and 60 Day
AInitial3Yes

No

3     NoYes
AFinal6NoNo      NoYes
AMajor Event7NoNo      NoYes
A60-Day6NoNo  6   NoYes
A180-Day3NoNo      NoYes

 

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!
 
 
 
 
1 REPLY 1
Eyelyn9
Community Support
Community Support

Hi @samdep ,

 

Sorry for that I could not get your table corrcctly:

 

Eyelyn9_0-1642730343860.png

Please refer to the blog to learn How to provide sample data in the Power BI Forum  

And please try to simplified your description by provided some expected outputs in different situations. How to Get Your Question Answered Quickly

 

 

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors
Top Kudoed Authors