Adding a Filter to IF in DAX

[Anonymous]

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?

[WishAskedSooner]

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]

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 · Accepted solution]

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. 

[WishAskedSooner]

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)

[Ritaf1983]

Hi @Anonymous 

Please provide a workable sample data and your expected result from that. It is hard to figure out what you want to achieve from the description alone.  

https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

[Anonymous]

ID	Date1	Date2	Date3	Approval	Type
100	12/07/2024	27/12/2024	27/12/2024	Yes	A
101	27/12/2024	27/12/2024	27/12/2024	Yes	A
102	27/12/2024	12/07/2024		Yes	B
103	27/12/2024	27/12/2024		Yes	B
104	12/07/2024	27/12/2024	27/12/2024	No	A
105	27/12/2024	27/12/2024		Yes	A

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]

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

[Uzi2019]

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]

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.