Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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

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])))
Need Help

3 Replies

  • v-chuncz-msft's avatar
    v-chuncz-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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"))