Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
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.
Gabriela
Solved! Go to Solution.
Hi @gabriela
USe this instead
X_New = IF ( NOT ( ISBLANK ( Data[X] ) ), Data[X] + 3 )
Hi @gabriela
USe this instead
X_New = IF ( NOT ( ISBLANK ( Data[X] ) ), Data[X] + 3 )
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.
Thanks,
Gabriela
Hi @gabriela
The problem comes from the "=" OPERATOR
In DAX terms
BLANK() = 0 = true
but
ISBLANK(0)= FALSE
So formulas below give different results
=if('Data'[X] = BLANK (), BLANK(), 'Data'[X] + 3)
=if(ISBLANK('Data'[X]) , BLANK(), 'Data'[X] + 3)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |