Forum Discussion
Solved
Creating new column without selected values from original column
Hi all, I'm trying to use DAX to remove the values in each row of this column that have multiples and then place the remaining values in a new column. E.g if 20 appears twice in a row then remove...
AB174 Sure:
Column 2 = VAR __Column1 = [Column1] VAR __Num = LEN(__Column1) - LEN(SUBSTITUTE(__Column1, ",", "")) + 1 VAR __TableText = SUBSTITUTE(__Column1, ",", "|") VAR __Table = ADDCOLUMNS( GENERATESERIES(1, __Num, 1), "__Value", PATHITEM(__TableText, [Value]) ) VAR __Table1 = ADDCOLUMNS( __Table, "__Num", VAR __Value = [__Value] VAR __Result = COUNTROWS(FILTER(__Table, [__Value] = __Value)) RETURN __Result ) VAR __Result = CONCATENATEX( FILTER( __Table1, [__Num] = 1 ), [__Value], ",") RETURN __Result
Greg_Deckler
Community Champion
Community ChampionAB174 Sure:
Column 2 =
VAR __Column1 = [Column1]
VAR __Num = LEN(__Column1) - LEN(SUBSTITUTE(__Column1, ",", "")) + 1
VAR __TableText = SUBSTITUTE(__Column1, ",", "|")
VAR __Table =
ADDCOLUMNS(
GENERATESERIES(1, __Num, 1),
"__Value", PATHITEM(__TableText, [Value])
)
VAR __Table1 =
ADDCOLUMNS(
__Table,
"__Num",
VAR __Value = [__Value]
VAR __Result = COUNTROWS(FILTER(__Table, [__Value] = __Value))
RETURN
__Result
)
VAR __Result = CONCATENATEX( FILTER( __Table1, [__Num] = 1 ), [__Value], ",")
RETURN
__ResultGreat! Thank you so much!
