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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
just3235
Helper I
Helper I

DAX Formula issue

Hi,

 

I am trying to run the following DAX code. However, it doesn't seem to work. 

 

 

I am using this nested IF statement, where the first argument is checking a condition, the second argument (if TRUE)  is printing some text, the third argument (if False) is running the next IF statement.

 

for ex. IF "Days since financial statement > 90days" AND "Days since financial statement > 60" AND "Days since financial statement > 30") is true, then print "Greater than 90days", but if that condition is false, then check IF "Days since financial statement < 90days" AND "Days since financial statement > 60" AND "Days since financial statement > 30") is true, then print "Greater than 60days"..... and so on...

 

Here's the code

 

 

IF(
AND(DaysSince[Days since Financial Statement] > 90, DaysSince[Days since Financial Statement] > 60, DaysSince[Days since Financial Statement] > 30, DaysSince[Days since Financial Statement] > 7,
DaysSince[Days since Financial Statement] <=7), "Greater than 90days",
IF(
AND(DaysSince[Days since Financial Statement] < 90, DaysSince[Days since Financial Statement] > 60, DaysSince[Days since Financial Statement] > 30, DaysSince[Days since Financial Statement] > 7,
DaysSince[Days since Financial Statement] <=7), "Greater than 60days",
IF(
AND(DaysSince[Days since Financial Statement] < 90, DaysSince[Days since Financial Statement] < 60, DaysSince[Days since Financial Statement] > 30, DaysSince[Days since Financial Statement] > 7,
DaysSince[Days since Financial Statement] <=7), "Greater than 30days",
IF(
AND(DaysSince[Days since Financial Statement] < 90, DaysSince[Days since Financial Statement] < 60, DaysSince[Days since Financial Statement] < 30, DaysSince[Days since Financial Statement] > 7,
DaysSince[Days since Financial Statement] <=7), "Greater than 7days",
IF(
AND(DaysSince[Days since Financial Statement] < 90, DaysSince[Days since Financial Statement] < 60, DaysSince[Days since Financial Statement] < 30, DaysSince[Days since Financial Statement] < 7,
DaysSince[Days since Financial Statement] <=7), "Less than 7 days",-1)
)
)
)
)

 

Thank you in advance!

2 ACCEPTED SOLUTIONS
Anonymous
Not applicable

Probably better to use the SWITCH, TRUE() pattern for that many ifs.  Give the following a try and see if that work:

SWITCH(
	TRUE(),
	[MEASURE] > 90, "Greater Than 90 Days",
	AND([Measure] < 90, [Measure] > 60), "Greater Than 60 Days",
	AND([Measure] < 60, [Measure] > 30), "Greater Than 30 Days",
	AND([Measure] < 30, [Measure] > 7), "Greater Than 7 Days",
	[Measure] <= 7,"Less Than 7 Days",
	0
)
	

View solution in original post

Looks like you may be making it too complicated.  Try using SWITCH() instead and simplify the conditions:

 

Test Column =
SWITCH (
    TRUE (),
    DaysSince[Days since Financial Statement] > 90, "Greater than 90 days",
    DaysSince[Days since Financial Statement] > 60, "Greater than 60 days",
    DaysSince[Days since Financial Statement] > 30, "Greater than 30 days",
    DaysSince[Days since Financial Statement] > 7, "Greater than 7 days",
    DaysSince[Days since Financial Statement] <= 7, "Less than 7 days",
    -1
)

Since SWITCH() will end evaluation once it hits a true, your code should be able to be simplified as above.  It's possible in all of the nesting, etc you introduced an odd character somewhere in one of the values.

 

Also note that in the case of getting to the ELSE part of the switch (-1), this will still be a text field, not a number.

 

Hope this helps

David

View solution in original post

8 REPLIES 8
ChrisMendoza
Resident Rockstar
Resident Rockstar

@just3235,

 

The first thing I see is your AND ( ) has multiple arguments when AND ( ) only allows for 2 arguments. You could nest AND ( ) or I believe you can use the '&&' withouth the AND ( )'s.

 

Is there not a better way to build your conditional logic? It seems like you're trying to evaluate a duration of time passing; maybe DATETIME functions could help in someway?

 

 

 






Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!



Hi Chris,

 

Thank you for responding.

 

You are right AND() takes in only 2 arguments. I modified my code and tried using both 'AND'  and '&&'.

 

But I am getting an error that states: "Expressions that yield variant data-type cannot be used to define calculated columns."

 

 

Here's what my a part of my new code looks like:

 

Test_Column =

IF(
((DaysSince[Days since Financial Statement] > 90) && (DaysSince[Days since Financial Statement] > 60)), "Greater than 90days",
        IF(
         ((DaysSince[Days since Financial Statement]) < 90 && (DaysSince[Days since Financial Statement] > 60)), "Greater than 60days",-1))
 
Any idea on what I could to resolve this?
 
 
 

Also, here's my code for 'AND':

 

 

 

IF(
AND(DaysSince[Days since Financial Statement] > 90, DaysSince[Days since Financial Statement] > 60), "Greater than 90days",
IF(
AND(DaysSince[Days since Financial Statement] < 90, DaysSince[Days since Financial Statement] > 60), "Greater than 60days",
IF(
AND(DaysSince[Days since Financial Statement] < 60, DaysSince[Days since Financial Statement] > 30), "Greater than 30days",
IF(
AND(DaysSince[Days since Financial Statement] < 30, DaysSince[Days since Financial Statement] > 7), "Greater than 7days",
IF(
DaysSince[Days since Financial Statement] <=7, "Less than 7 days",-1)
)
)
)
)

Looks like you may be making it too complicated.  Try using SWITCH() instead and simplify the conditions:

 

Test Column =
SWITCH (
    TRUE (),
    DaysSince[Days since Financial Statement] > 90, "Greater than 90 days",
    DaysSince[Days since Financial Statement] > 60, "Greater than 60 days",
    DaysSince[Days since Financial Statement] > 30, "Greater than 30 days",
    DaysSince[Days since Financial Statement] > 7, "Greater than 7 days",
    DaysSince[Days since Financial Statement] <= 7, "Less than 7 days",
    -1
)

Since SWITCH() will end evaluation once it hits a true, your code should be able to be simplified as above.  It's possible in all of the nesting, etc you introduced an odd character somewhere in one of the values.

 

Also note that in the case of getting to the ELSE part of the switch (-1), this will still be a text field, not a number.

 

Hope this helps

David

Thank you for this solution!

Anonymous
Not applicable

General rule I like to follow is that if it's more than one IF, use SWITCH. So much easier to use and debug Smiley Happy

Anonymous
Not applicable

Probably better to use the SWITCH, TRUE() pattern for that many ifs.  Give the following a try and see if that work:

SWITCH(
	TRUE(),
	[MEASURE] > 90, "Greater Than 90 Days",
	AND([Measure] < 90, [Measure] > 60), "Greater Than 60 Days",
	AND([Measure] < 60, [Measure] > 30), "Greater Than 30 Days",
	AND([Measure] < 30, [Measure] > 7), "Greater Than 7 Days",
	[Measure] <= 7,"Less Than 7 Days",
	0
)
	

Yes it works! 

 

Thank you! 🙂

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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