If Statement Calculated Column Returning Incorrectly

[damilolaA]

Hello everyone, 

Can someone please explain why the calculated column returns false instead of blank when the qty is between 4 and 7? Thank you. 

[djurecicK2 · Accepted solution]

Also, blank= false when converting data type from text to true/false.

So if you have this:

then convert the data type to true/false, everything is false:

[damilolaA]

Right, so if the column is a boolean data type, then blank() is treated as false because it's only two values (True/False). By having false() as one of the results of the if statement, this makes PowerBI think that the column is a boolean data type. Thank you for your help!

[djurecicK2]

Ok, I think this is happening because you are trying to combine Blank() and False().

Below are some options that will work:

[damilolaA]

Thank you. Do you have any idea why we can't combine BLANK() and FALSE()?

[djurecicK2]

They are different data types.

True/False is binary and can only have 1 of 2 values.

If you really need the value to be false, you can try using "False" as a text value. 

[DataNinja777]

Hi @damilolaA ,

The issue you're encountering is due to a data type mismatch in your Qty column. Power BI is likely interpreting the numbers in that column as Text instead of as a Whole Number. When a DAX formula like 'Table'[Qty] > 3 compares a text value to a number, it doesn't perform a mathematical comparison, which is why every row incorrectly evaluates to FALSE.

The most effective way to solve this is to correct the data type at its source using the Power Query Editor. You can access this by clicking "Transform data" on the Home ribbon. Inside the editor, select the Qty column, right-click the header, choose "Change Type," and then select "Whole Number." After you click "Close & Apply," your original DAX formula will work perfectly without any changes because the column's data is now correctly formatted as a number.

If you are unable to modify the data type in Power Query, you can force the conversion within your DAX formula itself by wrapping the column name in the VALUE() function. This function converts a text string into a number. While this approach works, it is less efficient as the conversion must be performed for every row each time the calculation runs, so fixing the data type in Power Query remains the recommended solution.

is_Qty_Greater_Than_3 = IF(VALUE('Table'[Qty]) > 3, BLANK(), FALSE())

Best regards,

[damilolaA]

Thanks for responding. Qty column was already formatted as a whole number in Power Query. Also, the wrapping in the VALUE function isn't working either. All rows still return false. 

[djurecicK2]

Just checking- is Qty datatype text?

[damilolaA]

It was already formatted as a  whole number in Power Query