Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Adding a Filter to IF in DAX

I'm trying to add a filter to the following:

 

IF(MAX ('Table'[Date1]) <= TODAY() ||
    MAX ('Table'[Date2]) <= TODAY() ||
    MAX ('Table'[Date3]) <= TODAY() ||
    MAXX ('Table', 'Table'[Approval]) <> "Yes",
    1, 0)

I need a filter on the Date3 line so that 'Table'[Type] = "TypeA" and I cannot figure out how to do it. 

It feels like this should be really simple! Can anyone help please?
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Thanks for the help everyone. I have just solved my problem by creating a separate measure for the Type:

    Filter TypeA = IF( CONTAINS( 'Table', 'Table[Type], "TypeA", 1, 0)

    Then added that to my original measure, so it reads:

    IF(MAX ('Table'[Date1]) <= TODAY() ||
        MAX ('Table'[Date2]) <= TODAY() ||
        MAX ('Table'[Date3]) <= TODAY() && [Filter TypeA] = 1||
        MAXX ('Table', 'Table'[Approval]) <> "Yes",
        1, 0)


    I'm sure it's not the most elegant way to do it, but it works. 

9 Replies

  • Uzi2019's avatar
    Uzi2019
    Icon for Community Champion rankCommunity Champion
    2 years ago

    Hi Anonymous 

     

    Can you try this on date 3

    Max value =
    IF(MAX('Calendar'[Date])<=TODAY(),MAXX(FILTER(Table,Table[Brand]="ABCD"),1),0)
     
    I hope I answered your question!
     
     
    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Thanks, but that didn't work. Everything returned a value of 1. 

      More than willing to admit that's it's because I've done something wrong than there being any problem with your solution.

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago
      IDDate1Date2Date3ApprovalType
      10012/07/202427/12/202427/12/2024YesA
      10127/12/202427/12/202427/12/2024YesA
      10227/12/202412/07/2024 YesB
      10327/12/202427/12/2024 YesB
      10412/07/202427/12/202427/12/2024NoA
      10527/12/202427/12/2024 YesA

       

      I'm trying to return an value of 1 when :
      Date1 is in the past or blank OR
      Date2 is in the past or blank OR
      Date3 is in the past or blank and Type is A OR
      Approval is Yes

      In the example above, I would expect the following to happen:
      ID100 would return value of 1 becasue Date1 is in the past
      ID101 would return value of 0 because all dates are in the future and Approval is Yes
      ID102 would return value of 1 because Date2 is in the past
      ID103 would return value of 0 because Date3 is blank but Type is B
      ID104 would return value of 1 because Date1 is in the past and Approval is No
      ID105 would return value of 1 because Date3 is blank and Type is A

      Hope that makes more sense! Thank you.

      • Ritaf1983's avatar
        Ritaf1983
        Icon for Super User rankSuper User
        2 years ago

        Hi Anonymous 
        According to the wanted result the formula is ,

        Flag =
        if(SELECTEDVALUE('Table'[Date1])<TODAY() ||
        ISBLANK(SELECTEDVALUE('Table'[Date1]))||
        SELECTEDVALUE('Table'[Date2])<TODAY() ||
        ISBLANK(SELECTEDVALUE('Table'[Date2]))||
        (SELECTEDVALUE('Table'[Date2])<TODAY() ||
        ISBLANK(SELECTEDVALUE('Table'[Date3]))&& SELECTEDVALUE('Table'[Type])="A" &&
        SELECTEDVALUE('Table'[Approval])="Yes"),1,0)
        But note that approval "YES" is with and anf not with or according to :
        "ID101 would return value of 0 because all dates are in the future and Approval is Yes"

        The pbix is attached

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

  • WishAskedSooner's avatar
    WishAskedSooner
    Icon for Continued Contributor rankContinued Contributor
    2 years ago

    Try this:

     

    IF(MAX ('Table'[Date1]) <= TODAY() ||
        MAX ('Table'[Date2]) <= TODAY() ||
        AND(MAX ('Table'[Date3]) <= TODAY(), 'Table'[Type] = "TypeA") ||
        MAXX ('Table', 'Table'[Approval]) <> "Yes",
        1, 0)
  • WishAskedSooner's avatar
    WishAskedSooner
    Icon for Continued Contributor rankContinued Contributor
    2 years ago

    The short answer is: Yes, it makes sense. The long answer is, No, because all those conditions make my head hurt. The only tip I have is adding an ISBLANK() condition to your list of conditions. For example:

     

    OR(MAX ('Table'[Date1]) <= TODAY(), ISBLANK('Table'[Date1]))

     

    Maybe that will get you want you need, but this really goes beyond PowerBI and DAX and falls into the realm of overly bespoke requirements.

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Hi Anonymous ,

       

      Here I think you need to understand that Type =A is a juxtaposition to the computational logic on Date3 that needs to be satisfied at the same time, and if in the case of a more complex relationship, you can use OR() as well as AND() function to help sort out the computational logic.

      I think  Ritaf1983 needs to update the measure, although the results returned are correct, Date2 is used where Date3 should have been used.

      You can try code as below to create a measure.

      Measure:

       

      Flag = 
VAR _DATE1 =
    SELECTEDVALUE ( 'Table'[Date1] )
VAR _DATE2 =
    SELECTEDVALUE ( 'Table'[Date2] )
VAR _DATE3 =
    SELECTEDVALUE ( 'Table'[Date3] )
VAR _Type =
    SELECTEDVALUE ( 'Table'[Type] )
VAR _Approval =
    SELECTEDVALUE ( 'Table'[Approval] )
VAR _RESULT =
    IF (
        OR ( _DATE1 < TODAY (), ISBLANK ( _DATE1 ) )
            || OR ( _DATE2 < TODAY (), ISBLANK ( _DATE2 ) )
            || AND ( OR ( _DATE3 < TODAY (), ISBLANK ( _DATE3 ) ), _Type = "A" )
                || _Approval <> "Yes",
        1,
        0
    )
RETURN
    _RESULT

       

      Result is as below.

      Best Regards,
      Rico Zhou

       

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        Thanks for the help everyone. I have just solved my problem by creating a separate measure for the Type:

        Filter TypeA = IF( CONTAINS( 'Table', 'Table[Type], "TypeA", 1, 0)

        Then added that to my original measure, so it reads:

        IF(MAX ('Table'[Date1]) <= TODAY() ||
            MAX ('Table'[Date2]) <= TODAY() ||
            MAX ('Table'[Date3]) <= TODAY() && [Filter TypeA] = 1||
            MAXX ('Table', 'Table'[Approval]) <> "Yes",
            1, 0)


        I'm sure it's not the most elegant way to do it, but it works.