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
cole_lehmkuhler
Helper II
Helper II

IF statement in DAX problem/ not allowing me to use the less than (<) symbol

So I'm trying to create an IF statement in a new column in order to help break down a product line by division (Gas, Marine, Trans, Differ, Diesel) however when I'm trying to use the "<" it is giving me an error. I'll put a picture in here, but I might just blatantly doing something that I can't do in this DAX formula. Just wanting some input on whats wrong or if the formula I have put in here is even possible to get. ThanksCapture2.PNG

3 ACCEPTED SOLUTIONS
KHorseman
Community Champion
Community Champion

If you're using & as an AND condition it should be &&. Single & is for string concatenation. After each && you need to state the column you're comparing with the <, >, =, etc.

 

IF( Table[ColumnA] > 100 && Table[ColumnA] <= 1000...





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

wonga
Continued Contributor
Continued Contributor

@cole_lehmkuhler

 

In addition to @KHorseman's suggestion, another way to implement a logical AND would be:

 

IF(AND(comparison1, comparison2), resultifTrue, resultifFalse)

 

View solution in original post

Sean
Community Champion
Community Champion

@cole_lehmkuhler This should work... Let me know.

 

Column =
SWITCH (
    TRUE (),
    'FP200 Dump (2)'[STOCK_NUMBER] < 700000, "Gas",
    'FP200 Dump (2)'[STOCK_NUMBER] >= 700000
        && 'FP200 Dump (2)'[STOCK_NUMBER] <= 799999, "Marine",
    'FP200 Dump (2)'[STOCK_NUMBER] >= 800000
        && 'FP200 Dump (2)'[STOCK_NUMBER] <= 999999, "Gas",
    'FP200 Dump (2)'[STOCK_NUMBER] >= 1000000
        && 'FP200 Dump (2)'[STOCK_NUMBER] <= 2999999, "Diesel",
    'FP200 Dump (2)'[STOCK_NUMBER] >= 3000000
        && 'FP200 Dump (2)'[STOCK_NUMBER] <= 4999999, "Fuel",
    'FP200 Dump (2)'[STOCK_NUMBER] >= 5000000
        && 'FP200 Dump (2)'[STOCK_NUMBER] <= 6999999, "Trans",
    'FP200 Dump (2)'[STOCK_NUMBER] >= 7000000
        && 'FP200 Dump (2)'[STOCK_NUMBER] <= 7999999, "Differ",
    "Null"
)

View solution in original post

8 REPLIES 8
cole_lehmkuhler
Helper II
Helper II

Thanks for the help everyone! I just got back and tried out all of the different options and most of you were right and helpful! Thanks again!

Anonymous
Not applicable

For this example, couldnt you also use the new Conditional Column feature that was released in the April update:

 

CONDITIONAL COLUMNS

With this update, we’re making it extremely easy for users to create new columns in their queries based on values from a different column. A typical use case for this includes creating a set of “buckets” or “categories” based on ranges from a continuous value column. For example, categorizing a person’s BMI into “Underweight”, “Normal”, “Overweight” or “Obese” based on well-known BMI value ranges, or defining categories such as “This Week”, “Last Week”, etc. based on a Date column.

Previously, this could be achieved by creating Custom Columns and capturing the conditional logic with a set of If-Then-Else statements. These expressions can very soon become extremely complex to author and maintain, as new conditions are added.

Starting with the April update, users can now define a set of rules and output values for the new column based on values in other columns within their tables. This can be achieved via the new Conditional Columns dialog, available in the Query Editor under the “Add Column” tab in the ribbon.

 

Sean
Community Champion
Community Champion

@cole_lehmkuhler This should work... Let me know.

 

Column =
SWITCH (
    TRUE (),
    'FP200 Dump (2)'[STOCK_NUMBER] < 700000, "Gas",
    'FP200 Dump (2)'[STOCK_NUMBER] >= 700000
        && 'FP200 Dump (2)'[STOCK_NUMBER] <= 799999, "Marine",
    'FP200 Dump (2)'[STOCK_NUMBER] >= 800000
        && 'FP200 Dump (2)'[STOCK_NUMBER] <= 999999, "Gas",
    'FP200 Dump (2)'[STOCK_NUMBER] >= 1000000
        && 'FP200 Dump (2)'[STOCK_NUMBER] <= 2999999, "Diesel",
    'FP200 Dump (2)'[STOCK_NUMBER] >= 3000000
        && 'FP200 Dump (2)'[STOCK_NUMBER] <= 4999999, "Fuel",
    'FP200 Dump (2)'[STOCK_NUMBER] >= 5000000
        && 'FP200 Dump (2)'[STOCK_NUMBER] <= 6999999, "Trans",
    'FP200 Dump (2)'[STOCK_NUMBER] >= 7000000
        && 'FP200 Dump (2)'[STOCK_NUMBER] <= 7999999, "Differ",
    "Null"
)
wonga
Continued Contributor
Continued Contributor

@cole_lehmkuhler

 

In addition to @KHorseman's suggestion, another way to implement a logical AND would be:

 

IF(AND(comparison1, comparison2), resultifTrue, resultifFalse)

 

KHorseman
Community Champion
Community Champion

@wonga yep that is an option. The only problem is that you can only string together two conditions with AND(). Any more than two and you have to nest AND(<condition 1>, AND(<condition 2>, AND(<condition 3>, AND(<condition 4>, <condition 5>)))) which gets hard to read. I don't even know if I have the right number of parentheses there. && and || are as far as I can tell unlimited and easier to read. @cole_lehmkuhler has lots of conditions there.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




wonga
Continued Contributor
Continued Contributor

@KHorseman

 

Ah okay, I see, Yeah, your way is probably more efficient then since there are a lot of conditions to check.

KHorseman
Community Champion
Community Champion

I also don't like AND() because it's backwards syntax compared to English. You can read && statements straight through as "If condition 1 and condition 2..." as opposed to translating "If and condition 1, condition 2..."





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




KHorseman
Community Champion
Community Champion

If you're using & as an AND condition it should be &&. Single & is for string concatenation. After each && you need to state the column you're comparing with the <, >, =, etc.

 

IF( Table[ColumnA] > 100 && Table[ColumnA] <= 1000...





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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.