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
DrewCarousel
Frequent Visitor

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
v-eachen-msft
Community Support
Community Support

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
v-eachen-msft
Community Support
Community Support

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.
amitchandak
Super User
Super User

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

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

Anonymous
Not applicable

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

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.