cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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

Accepted Solutions
Highlighted
Super User I
Super User I

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

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

Highlighted
Continued Contributor
Continued Contributor

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

@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

Highlighted
Community Champion
Community Champion

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

@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
Highlighted
Super User I
Super User I

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

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

Highlighted
Continued Contributor
Continued Contributor

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

@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

Highlighted
Super User I
Super User I

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

@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!




Highlighted
Continued Contributor
Continued Contributor

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

@KHorseman

 

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

Highlighted
Super User I
Super User I

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

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!




Highlighted
Community Champion
Community Champion

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

@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

Highlighted
Resolver I
Resolver I

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

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.

 

Highlighted
Helper II
Helper II

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

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!

Helpful resources

Announcements
Super Users of the Quarter - Q2 2020

Super Users of the Quarter - Q2 2020

Who are our Super User Superstars? Who made it to the top of the leaderboards? Get the answers!

June 2020 Community Highlights

June 2020 Community Highlights

Featured community members, changes to the Community, and more! Read up on recent Power BI community news.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors