Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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. Thanks
Solved! Go to Solution.
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...
Proud to be a Super User!
In addition to @KHorseman's suggestion, another way to implement a logical AND would be:
IF(AND(comparison1, comparison2), resultifTrue, resultifFalse)
@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" )
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!
For this example, couldnt you also use the new Conditional Column feature that was released in the April update:
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.
@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" )
In addition to @KHorseman's suggestion, another way to implement a logical AND would be:
IF(AND(comparison1, comparison2), resultifTrue, resultifFalse)
@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.
Proud to be a Super User!
Ah okay, I see, Yeah, your way is probably more efficient then since there are a lot of conditions to check.
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..."
Proud to be a Super User!
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...
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
94 | |
75 | |
63 | |
62 |
User | Count |
---|---|
137 | |
105 | |
104 | |
80 | |
63 |