Forum Discussion
Help with ... "Expressions that yield variant data-type"
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.
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.
6 Replies
- rsbin
Community Champion
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,
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,
