Return "Yes" or "No" based on Multiple Conditions

[taskinosurname]

Hi Everyone,

I'm working on a collision dashboard/report where it would identify whether a driver requires retraining or not. The measure would return a value of "Yes" or "No" based on certain conditions. These conditions are as follows: Retraining is required for a driver with two non-preventable collisions within 18 months or if a collision is preventable. For everything else, the return value should be "No." I created a dax that worked for the first part (2 non-preventable over 18-mos) of the condition; however, I'm struggling with the 2nd part of the syntax. Please see below for reference:

Retraining Req'd =

var _Driver = MAX('NRV Collision'[Driver/Reported by])

var _Date = EDATE(LASTDATE('NRV Collision'[Date].[Date]),-18)

Var _Retrain = COUNTROWS(    

    FILTER(

        ALL('NRV Collision'),

        'NRV Collision'[Driver/Reported by] = _Driver && 'NRV Collision'[Type] = "Non Preventable" && 'NRV Collision'[Date].[Date] >=_Date

    )

)

RETURN

IF(_Retrain >= 2,"Yes","No")

Any assistance would be greatly appreciated. Thanks, in advance.

[v-zhangti · Accepted solution]

Hi, @taskinosurname 

You can try the following methods.
Sample data:

Measure:

Difference = IF(SELECTEDVALUE('Table'[Last Collision Date])=BLANK(),0,
ABS (DATEDIFF (SELECTEDVALUE ( 'Table'[Date] ),
              SELECTEDVALUE ( 'Table'[Last Collision Date] ),MONTH)))

Retraining Req'd = 
IF (SELECTEDVALUE ( 'Table'[Last Collision Date] ) <> BLANK ()
        && SELECTEDVALUE ( 'Table'[Type] ) = "Non Preventable"
        && [Difference] < 18,
    "Yes",
    IF (SELECTEDVALUE ( 'Table'[Last Collision Date] ) = BLANK ()
            && SELECTEDVALUE ( 'Table'[Type] ) = "Preventable",
        "Yes",
        "No"
    )
)

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

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

[taskinosurname]

Thanks a million. This is what I was looking for. Given that I'm still relatively a novice with DAX, I would've never thought to put a measure within a measure. Thanks again.

Warm regards,

Taski

[djurecicK2]

Hi @taskinosurname ,

 Maybe something like this using OR. Pleas double check the ().

Var _Retrain = COUNTROWS(    

    FILTER(

        ALL('NRV Collision'),

        OR(('NRV Collision'[Driver/Reported by] = _Driver && 'NRV Collision'[Type] = "Non Preventable" && 'NRV Collision'[Date].[Date] >=_Date),('NRV Collision'[Driver/Reported by] = _Driver && 'NRV Collision'[Type] = "Preventable"))

    )

)

https://learn.microsoft.com/en-us/dax/or-function-dax

Please accept as solution if this has answered the question- thanks!

[taskinosurname]

Thank you for the quick response. I appreciate it. 

I tried your possible solution with no success. Oddly enough, it's even counting Non-preventables' outside 18 months.