Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi All,
I wrote a formula to show NA if it is blank and if the date is below 31/3/2018, but I am getting the error
Expressions that yield variant data-type cannot be used to define calculated columns.
Specific Water Usage (litres/kWp) = IF('Cleaning Activity'[Specific_Water_Usage__c]<>BLANK(), 'Cleaning Activity'[Specific_Water_Usage__c], IF('Cleaning Activity'[Date_of_Cleaning__c]<DATE(2018,3,31), "NA",'Cleaning Activity'[Specific_Water_Usage__c]))
Can you please tell me how to put it in another way?
Solved! Go to Solution.
Change it to
Specific Water Usage (format) = FORMAT('Cleaning Activity'[Specific_Water_Usage__c], "")
without the word 'string'
try to return blank in place of 'NA'
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Still the same error.
I am trying to make the blank values to appear as NA. Please check my formula.
You can't have conflicting data types for the same column, in your case I am assuming it would Strings and Numbers
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
How to fix this?
Please help
If it is 0, people would think no water has been used.
If it's NA, then pweople don't have to poulate it?
Any other work around please?
On PowerBI you cant have 2 different datatype on the same column. @LivioLanzo also mentioned that.
So you have 2 options.
You either convert numbers to text or text to numbers. If you dont want to NAs to be zeros then you can convert numbers to text.
Hi All,
The existing numbers changed to something else?
Have you used the FORMAT formula like this?
FORMAT(ColumnName, "")
with double quotes at the end?
Hi
Please find the formula.
Specific Water Usage (format) = FORMAT('Cleaning Activity'[Specific_Water_Usage__c], "string")
Change it to
Specific Water Usage (format) = FORMAT('Cleaning Activity'[Specific_Water_Usage__c], "")
without the word 'string'
Awesome, Thank you so much.
One last question,
Is there anyways to calculate the average totoal for the column if it consits of both NA and number?
There may be a way to do it that but you need to try it yourself.
I think you need to write a DAX formulas where
1. All values in the column will be first converted to numbers (blanks, NAs, NULLs converted to Zeros)
2. Then Put the FORMAT function at the end, which will take all the results (numbers) and convert them to text.
So the DAX will start with FORMAT ( transformation to number ) and will have the transformation within it.
I hope this makes sense?
You can give it a try and see if it works
No problem@Kolumam
If the answer provided has fixed your issue please mark is as a solution so as to help other readers as well.
Thanks
Already marked as accept it as solution.
In case of no Water Usage it is best practice to just use 0. At most you could leave it blank
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
The thing is blank doesn't mean that it's really blank.
People are not filling it.
The filter Cleaning Date < 31/8/2018 will apply to the water usage if it's blank and assign NA. For the dates after 31/8/2018, if it's blank people have to fill in.
That's the reason.
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 |
---|---|
109 | |
101 | |
84 | |
79 | |
69 |
User | Count |
---|---|
120 | |
110 | |
95 | |
82 | |
77 |