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
Labraham36
Helper I
Helper I

PROBLEMS WITH IF AND DATEDIFF

Hello friends ! I have a question: I need to make a DAX function that shows me an interval of days between two dates and at the same time filters the values ​​since I don't want it to show me this value if a certain field is empty. The function I have created is:

Dias_Vencimiento = IF('Consulta SLA'[dld_chk_codigo] = "",DATEDIFF('Consulta SLA'[FechaVencimiento_ML].[Date],TODAY(),DAY),"")

However, when I run it I get the following error:"Expressions that produce Variant data types cannot be used to define calculated columns." If I only do the "DATEDIFF" function I have no problems, but there is no way to filter it. Do you have any idea that I'm
doing wrong? I understand that it should be possible ... Thank you!

1 ACCEPTED SOLUTION
harshnathani
Community Champion
Community Champion

Hi @Labraham36 ,

 

Try this

 

Dias_Vencimiento = IF('Consulta SLA'[dld_chk_codigo] = "",DATEDIFF('Consulta SLA'[FechaVencimiento_ML].[Date],TODAY(),DAY),BLANK())

 

or 

 

Dias_Vencimiento = IF('Consulta SLA'[dld_chk_codigo] = "",DATEDIFF('Consulta SLA'[FechaVencimiento_ML].[Date],TODAY(),DAY),0)

 

DATEDIFF returns an interger. Here in your formula you are trying to return an integer as well as text hence the  Expressions that produce Variant data types cannot be used to define calculated columns." error

 

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

View solution in original post

2 REPLIES 2
harshnathani
Community Champion
Community Champion

Hi @Labraham36 ,

 

Try this

 

Dias_Vencimiento = IF('Consulta SLA'[dld_chk_codigo] = "",DATEDIFF('Consulta SLA'[FechaVencimiento_ML].[Date],TODAY(),DAY),BLANK())

 

or 

 

Dias_Vencimiento = IF('Consulta SLA'[dld_chk_codigo] = "",DATEDIFF('Consulta SLA'[FechaVencimiento_ML].[Date],TODAY(),DAY),0)

 

DATEDIFF returns an interger. Here in your formula you are trying to return an integer as well as text hence the  Expressions that produce Variant data types cannot be used to define calculated columns." error

 

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

Thank you very much friend ! You're the best ! I was going crazy with this !!!!!

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.

Top Solution Authors