Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The 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.

Reply
kjaworski
Regular Visitor

DAX - multiple conditions

Hello 

 

I'm trying to do simple filtering using multiple conditions. At least I thought it would be easy.

 

Here are the columns:

 

AmountAmountLeftEndDatestatus
100502016.12.31closed
10002016.12.31closed
100502017.01.31active

 

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.

 

1 ACCEPTED 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?

View solution in original post

9 REPLIES 9
C4YNelis
Advocate III
Advocate III

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

Baskar
Resident Rockstar
Resident Rockstar

It working fine for me.1.JPG

 

 

 

 

 

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

 

powerbi-dax.PNG

 

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

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.