cancel
Showing results for 
Search instead for 
Did you mean: 

Switch evaluates blank VAR's in calculated columns as a wrong data type

I have a custom table to combine 6 source files. Each of the files have some information that other not have or actually do have. Here comes the Switch into place. For about 25 fields in the custom tables there is the Switch statement that refers to 6 VAR's containing minx expressions to grab the information from those 6 files. 

To reduce the problem to the source of the issue, I left out the minx expressions and only had the VAR's and Switch statement (1st picture). Below you can find the result of my experiments. This can be quite easily reproduced by just stuffing the expression into a calculated column. 

One of the solutions mentioned by others was to put the last VAR into the Switch statement as the last evaluated statement. But this doesn't seem to be always consistent (2nd picture). Most surprisingly is that a number substituted on the lone text value of one of the VAR's seem to solve the problem even when the data type of the column is Text (3rd picture). 

Untitled.png

Untitled4.png

Untitled5.png

 

Status: New
Comments
Community Support

@DouweMeer 

 

You may take a look at the following article.

https://www.sqlbi.com/articles/blank-handling-in-dax/

Post Prodigy

@v-chuncz-msft 

How would you explain the following behavior in a pre-defined text data type field? 

Untitled.png

Why isn't it giving me conflicting data type as the blank was earlier seen as a false value? Data type of the field is still text, like it was before. VAR a2 till a4 are still blanks. The Switch statement still has the same logic, but only the value of VAR a1 is now a number instead of a text. Somehow the expression is valid right now. How's that expected behavior?