topic Return "Yes" or "No" based on Multiple Conditions in DAX Commands and Tips

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

taskinosurname — Mon, 28 Nov 2022 22:27:03 GMT

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.

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

djurecic — Mon, 28 Nov 2022 23:01:10 GMT

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!

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

v-zhangti — Wed, 30 Nov 2022 05:23:36 GMT

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.

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

taskinosurname — Wed, 30 Nov 2022 19:49:16 GMT

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.

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

taskinosurname — Thu, 01 Dec 2022 03:17:59 GMT

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