Forum Discussion
E12345
Resolver II
Resolver IIDax measure needed
Hi! I need help formatting a text column. It seems so easy to do it in SQL using string functions, but I need help doing it using DAX: I have a column that has two problems - each value is a list ...
I realized you wanted DAX so here they are
CapitalizedFirstTwoCharacters CalcColumn = VAR CountOfComma = LEN ( 'Table'[MyColumn] ) - LEN ( SUBSTITUTE ( 'Table'[MyColumn], ",", "" ) ) VAR Numbers = GENERATESERIES ( 1, 2, 1 ) VAR Substituted = SUBSTITUTE ( 'Table'[MyColumn], ",", "|" ) VAR _Names = ADDCOLUMNS ( Numbers, "@name", VAR _item = PATHITEM ( Substituted, [Value] ) VAR _itemLength = LEN ( _item ) RETURN UPPER ( LEFT ( _item, 2 ) ) & LOWER ( RIGHT ( _item, _itemLength - 2 ) ) ) RETURN CONCATENATEX ( _Names, [@name], ", " )CapitalizedFirstTwoCharacters Measure = VAR _Value = MAX ( 'Table'[MyColumn] ) VAR CountOfComma = LEN ( _Value ) - LEN ( SUBSTITUTE (_Value, ",", "" ) ) VAR Numbers = GENERATESERIES ( 1, CountOfComma, 1 ) VAR Substituted = SUBSTITUTE (_Value, ",", "|" ) VAR _Names = ADDCOLUMNS ( Numbers, "@name", VAR _item = PATHITEM ( Substituted, [Value] ) VAR _itemLength = LEN ( _item ) RETURN UPPER ( LEFT ( _item, 2 ) ) & LOWER ( RIGHT ( _item, _itemLength - 2 ) ) ) RETURN CONCATENATEX ( _Names, [@name], ", " )
danextian
Super User
Super UserI realized you wanted DAX so here they are
CapitalizedFirstTwoCharacters CalcColumn =
VAR CountOfComma =
LEN ( 'Table'[MyColumn] ) - LEN ( SUBSTITUTE ( 'Table'[MyColumn], ",", "" ) )
VAR Numbers =
GENERATESERIES ( 1, 2, 1 )
VAR Substituted =
SUBSTITUTE ( 'Table'[MyColumn], ",", "|" )
VAR _Names =
ADDCOLUMNS (
Numbers,
"@name",
VAR _item =
PATHITEM ( Substituted, [Value] )
VAR _itemLength =
LEN ( _item )
RETURN
UPPER ( LEFT ( _item, 2 ) )
& LOWER ( RIGHT ( _item, _itemLength - 2 ) )
)
RETURN
CONCATENATEX ( _Names, [@name], ", " )
CapitalizedFirstTwoCharacters Measure =
VAR _Value = MAX ( 'Table'[MyColumn] )
VAR CountOfComma =
LEN ( _Value ) - LEN ( SUBSTITUTE (_Value, ",", "" ) )
VAR Numbers =
GENERATESERIES ( 1, CountOfComma, 1 )
VAR Substituted =
SUBSTITUTE (_Value, ",", "|" )
VAR _Names =
ADDCOLUMNS (
Numbers,
"@name",
VAR _item =
PATHITEM ( Substituted, [Value] )
VAR _itemLength =
LEN ( _item )
RETURN
UPPER ( LEFT ( _item, 2 ) )
& LOWER ( RIGHT ( _item, _itemLength - 2 ) )
)
RETURN
CONCATENATEX ( _Names, [@name], ", " )
E12345Resolver II
Resolver IIThank you so much! You are so close, but this function drops one of the names on the list as you can see from your data sample (there is always one name missing if there is more than one name on the line). Can this function be updated? TY!