Forum Discussion

newpi's avatar
newpi
Icon for Helper V rankHelper V
4 years ago
Solved

How do I sort values in a column to be in the same order.

Hi,
I have a column with comma seperated values like

1.

a,b,c,d

2.

b,c,d,a

3.a,d,b,c
4.a,b,c
5.b,c,a

 

I don't care about the order. Rows 1,2 & 3 are the same and row 4&5 are the same. Just the alphabets are jumbled. I want to retain the rows but I want the values to be in the same order and not jumbled.. How do I organize this column so its not jumbled? 

Need Help
Show and Tell
Tips and Tricks

6 Replies

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hi newpi 
    You can create a new calculated column using DAX. Here is a sample file with the solution https://www.dropbox.com/t/1KQTM2trlplD0Gnw

    Values Correct Order = 
VAR String = Data[Values]
VAR ConvertPath = SUBSTITUTE ( String, ",", "|" )
VAR Length = PATHLENGTH ( ConvertPath )
VAR T1 = GENERATESERIES ( 1, Length, 1 )
VAR T2 = ADDCOLUMNS ( T1, "@Items", PATHITEM ( ConvertPath, [Value] ) )
RETURN
    CONCATENATEX ( T2, [@Items], ",", [@Items], ASC )

     

    • newpi's avatar
      newpi
      Icon for Helper V rankHelper V
      4 years ago

      tamerj1 I'm getting an error "The arguments in GenerateSeries function cannot be blank." 

      What am I missing? Also I'm creating a new column in the table and the using this formula and my values in the column are not of equal length, meaning its more like ( a, abc, ab) etc.

      use_cases correct order =
      VAR String = new_business_use_cases[use_cases]
      VAR ConvertPath = SUBSTITUTE ( String, ", ", "|" )
      VAR Length = PATHLENGTH ( ConvertPath )
      VAR T1 = GENERATESERIES ( 1, Length, 1 )
      VAR T2 = ADDCOLUMNS ( T1, "@Items", PATHITEM ( ConvertPath, [use_cases] ) )
      RETURN
      CONCATENATEX ( T2, [@Items], ", ", [@Items], ASC )
      • tamerj1's avatar
        tamerj1
        Icon for Community Champion rankCommunity Champion
        4 years ago

        newpi 
        You have blank values. Please use https://www.dropbox.com/t/47luIKUpiLZmAFIO

        Values Correct Order = 
VAR String = Data[Values]
VAR ConvertPath = SUBSTITUTE ( String, ",", "|" )
VAR Length = COALESCE ( PATHLENGTH ( ConvertPath ), 1 )
VAR T1 = GENERATESERIES ( 1, Length, 1 )
VAR T2 = ADDCOLUMNS ( T1, "@Items", PATHITEM ( ConvertPath, [Value] ) )
RETURN
    CONCATENATEX ( T2, [@Items], ",", [@Items], ASC )