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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

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
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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