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.
Can you please help?
Thanks

Answer 1

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

 

Answer 2

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
Thanks for your help

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

Answer 3

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.

If you have any questions, please be feel free to ask.

Regards,
Angelia

Answer 4

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?

Answer 5

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.

---

http://darren.gosbell.com - please mark correct answers

Answer 6

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
Thanks for your help

Answer 7

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



---

http://darren.gosbell.com - please mark correct answers

Answer 8

thanks darren. this doesn't work for me. would you know why?

Answer 9

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.

---

http://darren.gosbell.com - please mark correct answers

Answer 10

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 

---

http://darren.gosbell.com - please mark correct answers