I have a data with many missing values for variable X and I'd like to do a simple transformation of this variable such as adding 3 to all values.
I tried the formula below:
X_new = if('Data'[X] = BLANK (); BLANK(); 'Data)'[X] + 3)
And it works almost perfectly (returns BLANK for those rows which have missing value) but the problem is that now also the rows with value = 0 are treated as BLANK. So, in the end, I have more missing values for variable X_new than for original variable X.
I'd appreciate suggestion how to deal with this problem.
Solved! Go to Solution.
Thank you @Zubair_Muhammad for such a quick reply. It worked perfectly!
The follow-up question: do you know why 0 were treated as missing values (BLANK) with my formula? I'm trying to understand the syntax of DAX.
The problem comes from the "=" OPERATOR
In DAX terms
BLANK() = 0 = true
So formulas below give different results
=if('Data'[X] = BLANK (), BLANK(), 'Data'[X] + 3)
=if(ISBLANK('Data'[X]) , BLANK(), 'Data'[X] + 3)