What will be the appropriate DAX measure to use LEFT, MID, and RIGHT to live data set

[NDDD]

Good day, 

 First I use live data set and it is not allowed to add columns or modify any tables. When I try to use MID, RIGHT, LEFT gives me the error "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value". Colum that I want to extract values is measure formatted as a number. 

Suggestions?

Thanks 

[NDDD]

HI @Samarth_18  

  I tried usning SUMX same error, here is excel example where Value is number and when used MID(A2,4,2)&"/"&RIGHT(A2,2)&"/"&MID(A2,2,2) New Vlaue will become a date. 

Value	New Value
1220511	05/11/22
1220519	05/19/22

Hope will help 

[Samarth_18]

@NDDD , Please try this.

Measure = 
var _year = MID(MAX('Table'[Value]),2,2)
var _day = right(MAX('Table'[Value]),2)
var _month = MID(MAX('Table'[Value]),4,2)
return _month&"/"&_day&"/"&_year

Output:-

[NDDD]

HI @Samarth_18 

  Same error "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value". Don't know if helps at the original BI file I have more columns. 

Thank you

[Samarth_18]

@NDDD Could you please share your code implementation?

[NDDD]

@Samarth_18  Belive the issue is that in my example "Value" is a measure, not a column, as MAX function will only accept column reference as an argument. 

"Value" measure is created to convert string text to number

SUMX ('Item Change Activity',IFERROR(VALUE('Item Change Activity'[New Value]),0))

Here is the measure as you suggested:

New Value as Date ND =                                                                                                          var _year = MID(MAX('Item Change Activity'[New Value as number ND]),2,2)                          var _day= RIGHT(max('Item Change Activity'[New Value as number ND]),2)                          var _month = mid(max('Item Change Activity'[New Value as number ND]),4,2)                      return _month&"/"&_day&"/"&_year

[Samarth_18]

@NDDD , I am not sure if I am getting it correctly but If you wanted to convert string to number then you can try int(value).

[NDDD]

@Samarth_18 I used measure above to convert string to number as int will consider only measure not column. I use a live connection and there are restrictions :(.

Your measure with MAX will work when there is column. In my case, there is measure that is used to convert string to a number. 

[Samarth_18]

Hi @NDDD ,

You are not allowed to use column directly in the measure.Hence you need to use aggregate functions like sum,max,min etc.

It would be helpful if you could share your formula what you are trying with sample data and expected output.

BR,

Samarth