Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Kolumam
Post Prodigy
Post Prodigy

Issue with Calculated column

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?

1 ACCEPTED SOLUTION

Change it to 

 

Specific Water Usage (format) = FORMAT('Cleaning Activity'[Specific_Water_Usage__c], "")

without the word 'string'

View solution in original post

18 REPLIES 18
LivioLanzo
Solution Sage
Solution Sage

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

@Kolumam

 

Does it have to be strictly NA?

Why dont you put 0 instead of NA?

 

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?

@LivioLanzo

 

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,

dadsa.JPGformula.JPG

 

 

 

 

 

 

 

 

 

The existing numbers changed to something else?

 

@Kolumam

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?

@Kolumam

 

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.