Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

The FabCon + SQLCon recap series starts April 14th at 8am Pacific. If you’re tracking where AI is going inside Fabric, this first session is a can't miss. Register now

Reply
Anonymous
Not applicable

Reporting constant errors:variant data-type cannot be used to define calculated columns

‎05-06-2024 12:50 AM

Hi,
I have a table named: FACT_Stock, I want to create a calculated column to define remaining shelf life (months).
Mine methodology is to use Column [EXP], [PictureDate], these two colum type is date, [EXP] is mixed with some blanks.
Formula of Remaining shelf life (months) is written as 

[RSL] =
VAR Months = IF(FACT_Stock[EXP] = BLANK(), BLANK(), DATEDIFF(FACT_Stock[PictureDate],FACT_Stock[EXP],MONTH))
RETURN
IF(
    Months = BLANK(),
    "Undefined",
    SWITCH(
        TRUE(),
        Months <= 0,
        "Expired",
        Months >= 48,
        "Not relevant",
        Months
    )
)

It keeps reporting errors: Expressions that yield variant data-type cannot be used to define calculated columns. I tried serveral ways to fix the error, such as use CONVERT formula to transform data type, but the error doesn't diappear

Labels:
Message 1 of 3
302 Views
0
2 REPLIES 2
PhilipTreacy
Super User
Super User

‎05-06-2024 03:14 AM

Hi @Anonymous 

 

Plese supply a representataive sample of your data (or all of it), so we can see what your working with.  Really hard to debug something without seeing the data.

 

Regards

 

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.

Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


Message 2 of 3
275 Views
0
Anonymous
Not applicable
In response to PhilipTreacy

‎05-06-2024 03:26 AM

Hi Philip,
Table is like this way

ItemCodeEXPSOHPictureDate
H0844900012021/11/14702024/3/31
H0845600012022/5/14602024/3/31
H0845600012022/3/14132024/3/31
H0845600012022/5/14402024/3/31
H0845600012999/12/31102024/3/31
H0845800012022/4/14602024/3/31
H0845800012022/6/14142024/3/31
H0845800012022/6/1462024/3/31
H0845800012023/4/18502024/3/31
H0845800012022/6/14152024/3/31
CBLM250 192024/3/31
CBLM250 362024/3/31
CBLM250T 12024/3/31
CBLM250T 72024/3/31
505512026/12/204392024/3/31
498312026/7/1838292024/3/31
498312026/7/1852024/3/31
505512026/6/21157222024/3/31
498312026/3/3112024/3/31
509412026/3/304962024/3/31
CL20811T 342024/3/31
CL20811T 7672024/3/31
CL20811TT 92024/3/31
H2820300012099/1/1122024/4/1
H2820500012099/1/1542024/4/1
H2850100012099/1/1152024/4/1
H3280100012099/1/198752024/4/1
Message 3 of 3
274 Views
0

Helpful resources

Announcements
New to Fabric survey Carousel

New to Fabric Survey

If you have recently started exploring Fabric, we'd love to hear how it's going. Your feedback can help with product improvements.

Power BI DataViz World Championships carousel

Power BI DataViz World Championships - June 2026

A new Power BI DataViz World Championship is coming this June! Don't miss out on submitting your entry.

Join our Fabric User Panel

Join our Fabric User Panel

Share feedback directly with Fabric product managers, participate in targeted research studies and influence the Fabric roadmap.

March Power BI Update Carousel

Power BI Community Update - March 2026

Check out the March 2026 Power BI update to learn about new features.

View All