Getting error - expressions that yield variant data type cannot be used in calculated column

[Anonymous]

Hi,

I need to display only month values when the date diff between 2 dates is >=90days and display only day values when the date diff is >=4 days, display hour values when the date diff is >= 1 days else display the default date value.

To achieve this , I used a calcuated column named Time level as shown below 

Time Level = IF(DATEDIFF(min('Fact'[Production Date-Time]),max('Fact'[Production Date-Time]),DAY)>=90,"M",
(IF(DATEDIFF(min('Fact'[Production Date-Time]),max('Fact'[Production Date-Time]),DAY)>=4,"D",
(IF(DATEDIFF(min('Fact'[Production Date-Time]),max('Fact'[Production Date-Time]),DAY)>=1,"H","E")))

After that i tried using the time level column into another calculated column , when i used the day function, am getting the variant data type error. Please help.

custom date xaxis = if ('Fact'[Time Level] = "M",'Fact'[Production Date-Time].[Month],

(if('Fact'[Time Level]="D",'Fact'[Production Date-Time].[Day],'Fact'[Production Date-Time])))

[v-chuncz-msft]

@Anonymous 

You may try adding CONVERT.

        IF (
            'Fact'[Time Level] = "D",
            CONVERT ( 'Fact'[Production Date-Time].[Day], STRING ),
            CONVERT ( 'Fact'[Production Date-Time], STRING )
        )

[Anonymous]

Hi,

I used the convert function and now my clculated column does not throw the variant data type error but its not displaying the correct value, its diaplaying H by default. Ideally if the timelevel selection is "M" only month value should be displayed, when the time level is "D" it should display days but it is always dispalying "H".

Please find the code below and the screenshot shows the value of timelevel, date diff and the claculated column which is not

showing correctly.

test prod date selection = if([Time level]="M",'Fact'[prod-month],

if([Time level]="D",convert('Fact'[prod-day],STRING),"H"))

[amitchandak]

Seem like Day is int and other is text

add & "" after the [Day]