Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hello
I'm trying to do simple filtering using multiple conditions. At least I thought it would be easy.
Here are the columns:
Amount | AmountLeft | EndDate | status |
100 | 50 | 2016.12.31 | closed |
100 | 0 | 2016.12.31 | closed |
100 | 50 | 2017.01.31 | active |
I try to make DAX for Status column, which would work simple way:
if Amount <> 0 and AmountLeft > 0 and EndDate > TODAY - status is active
if any of conditions are not fulfilled, status is closed
I tried to use:
Status = IF(Query1[Amount] = 0 || Query1[AmountLeft] < 0 || Query1[EndDate] <TODAY(); "CLOSED"; "active")
but it doesn't work properly.
I'd be obliged if someone could help.
Solved! Go to Solution.
I guess it works fine now in this version
Status = IF(Query1[BonusAmount] = 0 || Query1[BonusLeft] < 0 || Query1[EndDate] < TODAY() && Query1[EndDate] <> BLANK(); "CLOSED"; "active")
Am I right?
I think you want to write it like this:
status = If(Query1[BonusAmount] = 0 || Query1[BonusLeft] <= 0 || (Query1[EndDate] < TODAY() || Isblank(Query1[EndDate])),"Closed","Active")
It's a subtle difference, but otherwise you might still see the wrong lines when your BonusLeft ends up 0. (this scenario was not present in your sample data).
Best Regards,
Niels
It working fine for me.
Check the date coolumn which datatype it is ?
create Calculated column .
Let me know what error if u getting.
Hello
Here is result in my powerbi
The "EndDate" column is Date type.
U have Blank in End_Date column that is the problem.
If it is blank , then what u have to do ? u have to add that condition too.
Ups.
If the EndDate is blank, it should be seen as EndDate > TODAY
I guess it works fine now in this version
Status = IF(Query1[BonusAmount] = 0 || Query1[BonusLeft] < 0 || Query1[EndDate] < TODAY() && Query1[EndDate] <> BLANK(); "CLOSED"; "active")
Am I right?
Status =if ( Isblank(Query1[EndDate]) , "Active", IF(Query1[BonusAmount] = 0 || Query1[BonusLeft] < 0 || Query1[EndDate] < TODAY(), "CLOSED", "Active"))
Try this this will help u
Thanks a lot! It works
Hi @kjaworski,
I'm guessing something went wrong with my earlier reply, as I cannot see it in the topic, however, if I'm mistaken, forgive me for the double post.
I just wanted to add to the previous solution. I added a small nuance to the formula, as you have a mistake when the BonusLeft value = 0. I believe you wanted to set this value to "Closed", but right now it might still remain "Active".
status = IF(Query1[BonusAmount] = 0 || Query1[BonusLeft] <= 0 || (Query1[EndDate] < TODAY() || Isblank(Query1[EndDate]));"Closed";"Active")
Best regards,
Niels
User | Count |
---|---|
120 | |
69 | |
68 | |
57 | |
50 |
User | Count |
---|---|
166 | |
82 | |
68 | |
65 | |
54 |