Help with ... "Expressions that yield variant data-type"

[Anonymous]

I am trying to mix numbers and text inside a column.  I already have the datatype as text and the format as text.  What am I doing wrong?  Do I need to use DAX to convert years and month numbers to text too?

I am using Direct Query so Convert is not working.

[Anonymous]

I know I can change the text "test" to a number.  The problem I have is that the date 1/1/2022 has the same value for the day(1) and the month(1).  I need them to be discrete.  I got around this by adding 100 to the day but now the bar chart shows 101, 102, 103, for the days I am trying to show.  It is confusing.  (again i am using direct query)

[rsbin]

@Anonymous ,

Based on your picture, this is what I'm guessing that you want:

DateTime	DateDisplay
1-Jan-17	2017
1-Jan-18	2018
1-Jan-19	2019
1-Jan-20	2020
1-Jan-21	2021
1-Jan-22	Jan
1-Feb-22	Feb
1-Mar-22	Mar
1-Sep-22	01
2-Sep-22	02

To get, please use the following calculated column:

DateDisplay = SWITCH(
                 TRUE(),
                 YEAR( [DateTime] ) - YEAR( Today() ) < 0, FORMAT( YEAR( [DateTime] ), "" ),
                 MONTH( [DateTime] ) - MONTH( Today() ) < 0, FORMAT( [DateTime], "mmm" ),
                 FORMAT( [DateTime], "dd" ))

Does this get you what you need?

Regards,

[Anonymous · Accepted solution]

I came up with this.

SpecialDate2 =

If(

    YEAR('MX ProductionDetails'[DateTime])-Year(TODAY())<0,

    CONCATENATE(YEAR('MX ProductionDetails'[DateTime])," "),

    IF(

        Month('MX ProductionDetails'[DateTime])-MONTH(TODAY())<0,

        SWITCH(Month('MX ProductionDetails'[DateTime]),

               1, "JAN", 2, "FEB", 3, "MAR", 4, "APR"  

               , 5, "MAY", 6, "JUN", 7, "JUL", 8, "AUG"  

               , 9, "SEP", 10, "OCT", 11, "NOV", 12, "DEC"  

               , "UNK" ),

        CONCATENATE(" ",day('MX ProductionDetails'[DateTime]))

        ))

Here is the graph.

[rsbin]

@Anonymous ,

My next step was to try to work something in Power Query,

but as long as you got a solution for yourself, that is all well and good.

All the Best,

[Anonymous]

Thank you for the effort.  Format does not work with DirectQuery.

This is what I am looking for

2019
2020
2021
Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

[rsbin]

@Anonymous ,

You are correct.  Power BI does not allow you to mix numbers and text in the same column.

Simply change "test" to a number such as 0, -1, or 99.

Regards,