cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Kolumam Member
Member

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

Accepted Solutions
themistoklis New Contributor
New Contributor

Re: Issue with Calculated column

Change it to 

 

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

without the word 'string'

18 REPLIES 18
LivioLanzo Super Contributor
Super Contributor

Re: Issue with Calculated column

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!  

Kolumam Member
Member

Re: Issue with Calculated column

Still the same error.

 

I am trying to make the blank values to appear as NA. Please check my formula.

LivioLanzo Super Contributor
Super Contributor

Re: Issue with Calculated column

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!  

Kolumam Member
Member

Re: Issue with Calculated column

How to fix this?

 

Please help

themistoklis New Contributor
New Contributor

Re: Issue with Calculated column

@Kolumam

 

Does it have to be strictly NA?

Why dont you put 0 instead of NA?

 

Kolumam Member
Member

Re: Issue with Calculated column

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 Super Contributor
Super Contributor

Re: Issue with Calculated column

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!  

themistoklis New Contributor
New Contributor

Re: Issue with Calculated column

@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.

 

 

Kolumam Member
Member

Re: Issue with Calculated column

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
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 39 members 1,071 guests