Reply
Regular Visitor
Posts: 39
Registered: ‎05-19-2016
Accepted Solution

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


Accepted Solutions
Super User
Posts: 1,181
Registered: ‎12-29-2015

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

[ Edited ]

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

View solution in original post

Established Member
Posts: 167
Registered: ‎03-23-2016

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

[ Edited ]

@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

Super Contributor
Posts: 2,112
Registered: ‎08-11-2015

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

[ Edited ]

@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


All Replies
Super User
Posts: 1,181
Registered: ‎12-29-2015

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

[ Edited ]

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

Established Member
Posts: 167
Registered: ‎03-23-2016

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

[ Edited ]

@cole_lehmkuhler

 

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

 

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

 

Super User
Posts: 1,181
Registered: ‎12-29-2015

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

[ Edited ]

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

Established Member
Posts: 167
Registered: ‎03-23-2016

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.

Super User
Posts: 1,181
Registered: ‎12-29-2015

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

Super Contributor
Posts: 2,112
Registered: ‎08-11-2015

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

[ Edited ]

@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"
)
Member
Posts: 46
Registered: ‎01-25-2016

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
Regular Visitor
Posts: 39
Registered: ‎05-19-2016

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!