Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
I sometimes have a working calculated columns where I have a lengthy switch statment. I occasionally have to update this logic and sometimes this column throws a weird error. Prior it throwing an error, I have text and blank answers. I've made 100% sure all posible outcomes are actual text or simply blank().
Today again, I'm hit with data-type error after I changed one of the text results into a blank() result. If I change the blank() to "", it will no longer throw the error. Which is weird, because there are plenty other results in the switch statement that result in a blank() value.
Switch ( true()
, boolean expression , blank()
, boolean expression , "Text result 1"
, boolean expression , "Text result 2"
, blank()
)
The above doesn't return an error. But after I modify the above to:
Switch ( true()
, boolean expression , blank()
, boolean expression , "Text result 1"
, boolean expression , blank()
, blank()
)
It does return an error, which is weird.
So it is now:
Switch ( true()
, boolean expression , blank()
, boolean expression , "Text result 1"
, boolean expression , ""
, blank()
)
Anyone had that before?
Solved! Go to Solution.
Hi @DouweMeer ,
There's no official explanation for this in the documentation at the moment, but I've done some testing of my own and here are my results:
I've found that your reported error occurs when, and only when, the last determination condition returns BLANK() (non-others) and the data type of the content returned by the preceding determination condition is either text or boolean:
But if the last determination condition is not BLANK(), then this error is not reported:
Or the last judgment condition returns BLANK() but the previous judgment condition returns a number or date (date/time) type, no error will be reported, but if the previous judgment condition returns a date (date/time) type, the computed column will ultimately be determined to be of number type:
Based on the above test results, I have summarized the following two patterns for myself:
1. Because BLANK() itself is not of any data type, when there are multiple judgment conditions, the BLANK() function determines the data type of BLANK() based on the data type of the content returned by the judgment condition that follows the one it is in.
2. If BLANK() is in the last judgment condition (non-others), then it will be interpreted preferentially as a number type. This also explains the error if the content you previously returned was of type text.
Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
👉Power BI needs to know what type of data to expect from your SWITCH statement. If some results return text while others return BLANK(), it can throw an error. Make sure all outcomes of your SWITCH statement return the same data type.
When returning empty values, use BLANK() instead of "" (empty string). The latter is treated as text, while BLANK() is a distinct data type. If you have other results that return text, using BLANK() can lead to type mismatches.
You can also temporarily add VAR statements to store intermediate results for easier debugging.
Ensure that each boolean expression is valid and that it’s not returning unexpected results that could lead to errors.
Perhaps you misunderstood. "" (empty string), the one you propose to avoid, was the only solution as blank() was not accepted.
I know, it doesn't make sense.
Hi @DouweMeer ,
There's no official explanation for this in the documentation at the moment, but I've done some testing of my own and here are my results:
I've found that your reported error occurs when, and only when, the last determination condition returns BLANK() (non-others) and the data type of the content returned by the preceding determination condition is either text or boolean:
But if the last determination condition is not BLANK(), then this error is not reported:
Or the last judgment condition returns BLANK() but the previous judgment condition returns a number or date (date/time) type, no error will be reported, but if the previous judgment condition returns a date (date/time) type, the computed column will ultimately be determined to be of number type:
Based on the above test results, I have summarized the following two patterns for myself:
1. Because BLANK() itself is not of any data type, when there are multiple judgment conditions, the BLANK() function determines the data type of BLANK() based on the data type of the content returned by the judgment condition that follows the one it is in.
2. If BLANK() is in the last judgment condition (non-others), then it will be interpreted preferentially as a number type. This also explains the error if the content you previously returned was of type text.
Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
114 | |
95 | |
86 | |
78 | |
66 |
User | Count |
---|---|
151 | |
121 | |
111 | |
107 | |
95 |