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

Expressions that yield variant data-type cannot be used to define calculated columns

Dear Community,

 

I have the below formula that is returning the above error - please can you help me get rid of the issue?

I'm trying to achieve a column that gives me a date & time field.

 

Formats:

Channel = Text

Parent Account = Text

Submitted = Date

SLA = Date&Time

 

CalcSLA = if('Master Table'[Channel]="IE Own Network",if(OR('Master Table'[Parent Account]="CUSTOMER A",'Master Table'[Parent Account]="CUSTOMER B"),format('Master Table'[Submitted],"General Number")+0.729167,if('Master Table'[Parent Account]="CUSTOMER C",format('Master Table'[Submitted],"General Number")+0.729167,format('Master Table'[Submitted],"General Number")+0.5)),if('Master Table'[Channel]="UK Own Network - NIGHT",format('Master Table'[Submitted],"General Number")+format('Master Table'[SLA Temp],"General Number"),format('Master Table'[SLA],"General Number")))
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Expressions that yield variant data-type cannot be used to define calculated columns

Hi @DrewCarousel ,

 

You could refer to the following DAX:

CalcSLA =
IF (
    'Master Table'[Channel] = "IE Own Network",
    IF (
        OR (
            'Master Table'[Parent Account] = "CUSTOMER A",
            'Master Table'[Parent Account] = "CUSTOMER B"
        ),
        VALUE ( FORMAT ( 'Master Table'[Submitted], "General Number" ) ) + 0.729167,
        IF (
            'Master Table'[Parent Account] = "CUSTOMER C",
            VALUE ( FORMAT ( 'Master Table'[Submitted], "General Number" ) ) + 0.729167,
            VALUE ( FORMAT ( 'Master Table'[Submitted], "General Number" ) ) + 0.5
        )
    ),
    IF (
        'Master Table'[Channel] = "UK Own Network - NIGHT",
        VALUE (
            FORMAT ( 'Master Table'[Submitted], "General Number" )
                + FORMAT ( 'Master Table'[SLA Temp], "General Number" )
        ),
        VALUE ( FORMAT ( 'Master Table'[SLA Temp], "General Number" ) )
    )
)

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.

View solution in original post

4 REPLIES 4
Highlighted
Super User IX
Super User IX

Re: Expressions that yield variant data-type cannot be used to define calculated columns

@DrewCarousel , Make sure all of them return text add & "" to make sure in all if and else



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
New Member

Re: Expressions that yield variant data-type cannot be used to define calculated columns

Hi Amit,

 

Thank you for this.

 

Please could you edit my original formula or give more guidance on where to place the &"".

 

Also to confirm, this will still add the decimal numbers to the original dates? To reiterate, I'm looking to get a date and time output.

 

Best regards,

 

Drew

Highlighted
Helper V
Helper V

Re: Expressions that yield variant data-type cannot be used to define calculated columns

@DrewCarousel : considering breaking the formula into multiple measures (a,b,c) so you can know exactly where the error is occurring. Then have your last output measure equal measure A + measure B + Measure C.

Highlighted
Community Support
Community Support

Re: Expressions that yield variant data-type cannot be used to define calculated columns

Hi @DrewCarousel ,

 

You could refer to the following DAX:

CalcSLA =
IF (
    'Master Table'[Channel] = "IE Own Network",
    IF (
        OR (
            'Master Table'[Parent Account] = "CUSTOMER A",
            'Master Table'[Parent Account] = "CUSTOMER B"
        ),
        VALUE ( FORMAT ( 'Master Table'[Submitted], "General Number" ) ) + 0.729167,
        IF (
            'Master Table'[Parent Account] = "CUSTOMER C",
            VALUE ( FORMAT ( 'Master Table'[Submitted], "General Number" ) ) + 0.729167,
            VALUE ( FORMAT ( 'Master Table'[Submitted], "General Number" ) ) + 0.5
        )
    ),
    IF (
        'Master Table'[Channel] = "UK Own Network - NIGHT",
        VALUE (
            FORMAT ( 'Master Table'[Submitted], "General Number" )
                + FORMAT ( 'Master Table'[SLA Temp], "General Number" )
        ),
        VALUE ( FORMAT ( 'Master Table'[SLA Temp], "General Number" ) )
    )
)

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.

View solution in original post

Helpful resources

Announcements
Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Upcoming Events

Community Summit North America – Join Online!

Join this community-driven Power Platform digital event for unbiased support and problem-solving.

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Top Solution Authors