Forum Discussion
Solved
Dax 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], ", " )
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], ", " )
Thank 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!