Forum Discussion

lynnzrae's avatar
lynnzrae
Icon for Helper I rankHelper I
2 years ago
Solved

Return if one condition OR another is met

I am trying to create a column that returns if a project is "expired" or "not expired."  (My column C)

If the permit start date is less than (before) to the construction start date OR if the construction start date is blank and the permit start date is less than (before/has passed) today it is expired.

 

Permit Start DateConstruction Start DateNew Column (Expired or Not Expired)
What I am trying to create
6/24/2023 Expired
1/2/202212/30/2021Not Expired
5/3/20245/15/2024Expired
Formula with Multiple Conditions
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi, lynnzrae 

    This DAX only works in Measure creation, if you want to achieve this effect in Calculate column you can use the following DAX.

    DAX:

    Condition column = 
IF (
    [Permit Start] < [DateConstruction],
    "Expired",
    IF (
        [DateConstruction] = BLANK ()
            && [DateConstruction] < TODAY (),
        "Expired",
        "Not Expired"
    )
)

     

    Best Regards,
    Yang
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know.     Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

4 Replies

  • lynnzrae's avatar
    lynnzrae
    Icon for Helper I rankHelper I
    2 years ago

    the above didn't return the correct values but I massaged the two formulas together and got what I needed so I am good to go.  Thank you for your help!

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi, lynnzrae 

    You can try the following measure to solve the problem.

    Measure:

    Condition = 
VAR _permitStart =
    SELECTEDVALUE ( 'Table'[Permit Start] )
VAR _dateConstruction =
    SELECTEDVALUE ( 'Table'[DateConstruction] )
VAR _today =
    TODAY ()
VAR _result =
    IF (
        HASONEVALUE ( 'Table'[DateConstruction] ),
        IF (
            ( _permitStart < _dateConstruction )
                || (
                    ISBLANK ( _dateConstruction )
                        && _permitStart < _today
                ),
            "Expired",
            "Not Expired"
        )
    )
RETURN
    _result

     

    Best Regards,
    Yang
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know.     Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

    • lynnzrae's avatar
      lynnzrae
      Icon for Helper I rankHelper I
      2 years ago

      I copy and pasted and it accepted the formula but it returned empty cells

       

      I have my column with no errors but everything under the column header is blank

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi, lynnzrae 

        This DAX only works in Measure creation, if you want to achieve this effect in Calculate column you can use the following DAX.

        DAX:

        Condition column = 
IF (
    [Permit Start] < [DateConstruction],
    "Expired",
    IF (
        [DateConstruction] = BLANK ()
            && [DateConstruction] < TODAY (),
        "Expired",
        "Not Expired"
    )
)

         

        Best Regards,
        Yang
        Community Support Team

         

        If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
        If I misunderstand your needs or you still have problems on it, please feel free to let us know.         Thanks a lot!

        How to get your questions answered quickly --  How to provide sample data in the Power BI Forum