cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
thabz_mogale
Frequent Visitor

Evaluate all results for specific patient

Hi All

 

I have a list of patients and their various blood test results. Below is dummy data to futher explain what I need to evaluate.

 

NameMeasure TypeDate TakenResultMeasure Confilcts
Jason MoorHbA1c2018/06/1310.3Proximity Conflict
Jason MoorHbA1c2018/05/1310.1Proximity Conflict
Jason MoorHbA1c2019/10/137.6 
David MasonHbA1c2017/11/036.9Proximity Conflict
David MasonHbA1c2019/12/016.6 
David MasonHbA1c2017/10/038.6Proximity Conflict

 

I need a dax function that will evaluate HbA1c results for a patient and flag any results that were taken within 2 months of each other as Proximity Conflict.

 

In the table above, Jason Moor has 3 results, 2 of which are taken within a month of each other. I need to flag both of them as Proximity Conflicts because of this.

 

I tried using the below calculated column:

Measure Conflicts = SWITCH( TRUE(), 'Measure'[Measure Type] = "HbA1c" &&
'Measure'[Date] <= 'Measure'[HbA1c DateAdd]*, "Proximity Conflict (A1c)")
 
*'Measure{HbA1c DateAdd] = 
IF('Measure'[Measure Type] = "HbA1c",
DATE(YEAR('Measure'[OccurrenceDate]),MONTH('Measure'[OccurrenceDate])+2 ,DAY('Measure'[OccurrenceDate])))
 
What this calculated column does is evaluate only the row. I need it to evaluate all results for the measure type of HbA1c for the specific patient.
 
Please help.
2 REPLIES 2
Anonymous
Not applicable

@thabz_mogale Please try below measure

Measure 2 = 
VAR _date = MAX('Table'[Date Taken])
VAR _previousDate = CALCULATE(MAX('Table'[Date Taken]),FILTER(ALLEXCEPT('Table','Table'[Name]),'Table'[Date Taken]<_date && 'Table'[Measure Type]= "HbA1c"))
VAR _nextdate = CALCULATE(MIN('Table'[Date Taken]),FILTER(ALLEXCEPT('Table','Table'[Name]),'Table'[Date Taken]>_date  && 'Table'[Measure Type]= "HbA1c"))
RETURN IF(DATEDIFF(_date,_nextdate,MONTH)=1||DATEDIFF(_previousDate,_date,MONTH)=1,"Proximity Conflict")

@Anonymous thank you for taking the time to help. I tried your solution but I could not get it to work on my report. I even tried using it on a separate power bi report but it just would not load any results, it reqiures a lot of memory and would end up timing out. I cant even tell you if it works or not because it does not being back any results. I use a 8th Gen i7 8GB ram laptop and it would not load results. Intead it gives me this Error Message:
"There's not enough memory to complete this operation. Please try again later when there may be more memory available." 

Any way I can troubleshoot this performance issue?

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Top Solution Authors