Forum Discussion

taskinosurname's avatar
taskinosurname
Frequent Visitor
3 years ago
Solved

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

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's avatar
    v-zhangti
    3 years ago

    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.

     

4 Replies

  • v-zhangti's avatar
    v-zhangti
    Community Support
    3 years ago

    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's avatar
      taskinosurname
      Frequent Visitor
      3 years ago

      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

  • djurecic's avatar
    djurecic
    Super User
    3 years ago

    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"))
        )
    )
     
     
    Please accept as solution if this has answered the question- thanks!
     
    • taskinosurname's avatar
      taskinosurname
      Frequent Visitor
      3 years ago

      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.