# return blank() in Boolean column

• ### Question

• Hi,
I have a boolean column (B) defined as follows:
B = if([A],blank(),if(check,test1,test2)) where A is boolean too.
This formula returns FALSE when A is TRUE. However, I expect it to return blank() when A is TRUE.
Thanks

Wednesday, September 7, 2016 6:17 PM

• So it turns out this is a deliberate change (so maybe Marco's blog post was prophetic). In Excel 2016 and Power BI an implied cast of BLANK() to boolean returns FALSE.

So you have 2 options to work around this.

1. Use strings like "True" and "False". Although where I'm working at the moment we have a design standard that says that visible columns should not have values like true/false or yes/no. The should always have a descriptive name like "Premium Product"/"Standard Product" or "Overdue Payment" / "Ontime Payment"  etc. So that you know exactly what the values mean in a pivot table.

2. You can do a convoluted work around to create a dummy table that has a non-calculated boolean column with a blank value in it and create a calculated measure to return that "boolean blank" and then use that measure instead of the blank() function, but that is a lot of work and is going to perform worse than just using option 1

• Marked as answer by Tuesday, September 13, 2016 11:26 PM
Tuesday, September 13, 2016 10:26 PM

### All replies

• Hi yan_elf,

I calculate column in my local computer using my sample data ,and get the expected results without any issue.
I am confuse about the “check” in if function. Any value or expression in first argument must be evaluated to TRUE or FALSE. Please double check your formula if it’s correct.

If this is not what you what, please give us more details and sample data structure.

Regards,
Angelia

Thursday, September 8, 2016 6:53 AM
• Hi Angelia,

B = if([A],blank(),if(check,test1,test2))
check: [C]="X"
test1: [D]>5
test2: [D]>7

with columns A: boolean, C:text, D: whole number

nb: from my research so far, it seems that boolean columns do not allow blank values

Thursday, September 8, 2016 8:35 PM
• Hi yan_elf,

As your description, when you regard “test1” as a result when [C]="X" and [D]>5, regard “test2” as a result when [C]="X" and [D]>7 as well?

So I you can create a calculated column E as the result of “if(check,test1,test2)” in following screenshot.

Then you can create the B column as you expected.

>> It seems that Boolean columns do not allow blank values.

The Boolean columns just store TRUE and FALSE. In your scenario, the blank values are results of your calculated column, not stored in Boolean columns.

Regards,
Angelia
Friday, September 9, 2016 5:15 AM
• Thanks Angelia,
Actually the formula is:
B = if([A],blank(),if([C]="X",[D]>5,[D]>7))
with columns A: boolean, C:text, D: whole number
Can you help?

Friday, September 9, 2016 8:38 PM
• I get the same results as Angela, this logic works fine on my machine creating a simple PowerPivot model. Are you able to create a model in a new workbook with a few rows to test if your issue is not related to something else in your model? I don't have any problems returning BLANK() or TRUE() or FALSE() in a calculated column.

Friday, September 9, 2016 11:12 PM
• According to Powerpivot expert Marco Russo: 'Any column other than a Boolean (True/False) data type in DAX can have a blank value'
Angelia's column data type is text not boolean. Can you please try with:
B = if([A],blank(),if([C]="X",[D]>5,[D]>7))
with columns B: boolean, A: boolean, C:text, D: whole number

Saturday, September 10, 2016 12:13 AM
• @yan_elf - I'm friends with Marco, I pointed out that his article was not entirely accurate and provided some evidence and he's now updated it to reflect the fact that all columns in DAX can contain a blank value.

I've tried it with the exact parameters you've outlined and it looks like it's working fine (see the screenshot below)

﻿

Saturday, September 10, 2016 10:54 AM
• thanks darren. this doesn't work for me. would you know why?

• Edited by Monday, September 12, 2016 7:32 PM
Monday, September 12, 2016 7:24 PM
• Actually that looks like an issue with the latest tabular engine. I was testing in Excel 2013, but I can see from your screen shot that you are using Excel 2016. I also get the same behaviour as you in Power BI Desktop. I'll see if I can find out from some contacts at Microsoft if this is a deliberate change or a bug.

Monday, September 12, 2016 10:35 PM
• So it turns out this is a deliberate change (so maybe Marco's blog post was prophetic). In Excel 2016 and Power BI an implied cast of BLANK() to boolean returns FALSE.

So you have 2 options to work around this.

1. Use strings like "True" and "False". Although where I'm working at the moment we have a design standard that says that visible columns should not have values like true/false or yes/no. The should always have a descriptive name like "Premium Product"/"Standard Product" or "Overdue Payment" / "Ontime Payment"  etc. So that you know exactly what the values mean in a pivot table.

2. You can do a convoluted work around to create a dummy table that has a non-calculated boolean column with a blank value in it and create a calculated measure to return that "boolean blank" and then use that measure instead of the blank() function, but that is a lot of work and is going to perform worse than just using option 1