Calculated column based on multiple conditions - comparing rows by category in Power BI Dax-CodePudding

I need to create the calculated column - IsImproved - in the following calculated table - tbl_QOL (screenshot below)

It should compare AvgQOL (another calculated column) by MetricDate for each Client (EHRClientFK)

If - for any particular EHRClientFK AvgQOL value where IsDateMax = 1 is higher than AvgQOL value for IsDateMin = 1, then IsImproved value = 1; any other cases IsImproved = 0

For example:

EHRClientFK = 2666; AvgQOL for the (highest(max) date=1) = 3.66; 3.66 value is not higher than 3.83, means IsImproved for EHRClientFK 2666 = 0

EHRClientFK = 3444; AvgQOL for the (highest(max) date=1) = 3.5; 3.5 value IS HIGHER than 2, means IsImproved for EHRClientFK 3444 = 1

All shown in the picture below

Applied the following code. But it doesn't work correctly. It puts 1 even if AvgQOL for IsDateMax = 1 is less than AvgQOL for IsDateMin = 1, while I expect 0 there - like it is for AvgQOL = 3.66 (for the bigger date - 5/13/2021)

enter 
       IsImproved =                                                                         
               var _DateRecent = QOL[IsDateMax]                                                                                                                                                                                                        
               var _DateLatest = QOL[IsDateMin]

               var _max = calculate(max(QOL[AvgQOL]), FILTER(ALL('QOL'),  _DateRecent=1))                                                                                                                                                                   
               var _min = calculate(min(QOL[AvgQOL]), FILTER(ALL('QOL'),  _DateLatest=1))                                                                                                                                                                 
               var _min1 = calculate(max(QOL[AvgQOL]), FILTER(ALL('QOL'),  _DateRecent=0))                                                                                                                                                                 
               var _min2 = calculate(min(QOL[AvgQOL]), FILTER(ALL('QOL'),  _DateRecent=1))
          
               return if(_max > _min || _max >_min1 || _max > _min2, 1, 0)

I am super new to DAX, plz help!

CodePudding user response：

I'd prefer to use LOOKUPVALUE here:

VAR ClientFK = tbl_QOL[EHRClientFK]
VAR AvgQOLForMin =
    LOOKUPVALUE (
        tbl_QOL[AvgQOL],
        tbl_QOL[IsDateMin], 1,
        tbl_QOL[EHRClientFK], ClientFK
    )
VAR AvgQOLForMax =
    LOOKUPVALUE (
        tbl_QOL[AvgQOL],
        tbl_QOL[IsDateMax], 1,
        tbl_QOL[EHRClientFK], ClientFK
    )
RETURN
    0   ( AvgQOLForMax > AvgQOLForMin )