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
Rubal
Helper II
Helper II

DAX IF Formula error

Hi,

 

I am getting the below errors on the If Formula. Can anyone please help on how to resolve it?

Thank you. 

 

 

Vol(Cases / Suom) = if (t_sales_aggregate[Actual or Forecast]="Forecast", (t_sales_aggregate[Volume (Cases)] && t_sales_aggregate[Volume (SUOM)])*AVERAGE(t_sales_aggregate[Forecast Growth]), (t_sales_aggregate[Volume (Cases)]&&t_sales_aggregate[Volume (SUOM)]))
 
 
Variant data type error.jpg
12 REPLIES 12
Arjunarao
Resolver I
Resolver I

Hi,

To change the data type of a column from source is :

Go to Edit Query

Select the column

Go to Transform tool bar > Data Type and change 

 

Hi Arjun,

 

I don't even see the column into the query editor? Is this because it is a column measure?

Yes.
Specified columns in your expression.
Vol(Cases / Suom) =
IF (
t_sales_aggregate[Actual or Forecast] = "Forecast",
( t_sales_aggregate[Volume (Cases)] && t_sales_aggregate[Volume (SUOM)] )
* AVERAGE ( t_sales_aggregate[Forecast Growth] ),
( t_sales_aggregate[Volume (Cases)] && t_sales_aggregate[Volume (SUOM)] )
)

Hi Arjun,

 

Please excuse my ignorance. but where is the difference between the codes? seems exactly the same as mine but formatting. appreciate your help.

Hi @Rubal
You can use this link for DAX format.
https://www.daxformatter.com/

Hi Arjun,

 

Changing the formatting didn't solve the error 😞

Yes. But why dont you upload the sample file?

Try this:

 

Vol(Cases / Suom) =
IF (
t_sales_aggregate[Actual or Forecast] = "Forecast",
( t_sales_aggregate[Volume (Cases)] && t_sales_aggregate[Volume (SUOM)] )
* AVERAGE ( t_sales_aggregate[Forecast Growth] ),
FORMAT(( t_sales_aggregate[Volume (Cases)] && t_sales_aggregate[Volume (SUOM)] ), "General Number")
)

 

This will ensure that the data type of the result if true is the same as data type of the result if false i.e. Decimal Number in your case.

 

Give it a try.

 

Regards,

Tarun

Hi Tarun,

 

I have tried your piece of code and still showing me the same error. I just don't know what is going on with this 😞

 

variant data type 2.jpg

adityavighne
Continued Contributor
Continued Contributor

Hi @Rubal 

 

change the data type from Text to Wole Number.

The calculation columns you are doing in this formula.

 

ss.PNG

 

 

Hi Aditya,

 

Thanks for your comments. As I am new to Power BI I am not sure how to do it. As it is a calculated column I do not see it in the query editor. How and where can I change the data type? Your help is highly appreciated.

 

Thanks.

The error message indicates that the data type of the calculation for if true section is different than the data type for the if false section. Ensure that in an IF statement, the data type of the resulting expression is same for all cases.

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.