Forum Discussion

Devesh's avatar
Devesh
Frequent Visitor
4 years ago
Solved

split the amount value using comma separated value column dax

Hi All,   I am struggling with this simple problem. My data set: name amount a 500 b 500 c,d 500 e,f,g 600 h 1000 I,j,k,l 1000   The output I want: name amou...
Need Help
  • MFelix's avatar
    MFelix
    4 years ago

    Hi Devesh ,

     

    My error, did not notice the last part that you asked for the split check the formula and PBIX revised:

     

    Word List = 
VAR SplitByCharacter = ","
VAR Table0 =
    ADDCOLUMNS (
        GENERATE (
            Unpivot,
            VAR TokenCount =
                PATHLENGTH ( SUBSTITUTE ( Unpivot[name], SplitByCharacter, "|" ) )
            RETURN
                GENERATESERIES ( 1, TokenCount )
        ),
        "NameSplit", PATHITEM ( SUBSTITUTE ( Unpivot[name], SplitByCharacter, "|" ), [Value] ),
        "Numberofwords",
            LEN ( Unpivot[name] ) - LEN ( SUBSTITUTE ( Unpivot[name], ",", "" ) ) + 1
    )
RETURN
  SELECTCOLUMNS(  Table0, "AMount", Divide(Unpivot[amount], [Numberofwords]), "Name", [NameSplit])

     

     

Devesh's avatar
Devesh
Frequent Visitor
4 years ago

Hi,

The amount does not get divided using the formula you posted. It just copies the amount into all CSV values.

MFelix's avatar
MFelix
Super User
4 years ago

Hi Devesh ,

 

My error, did not notice the last part that you asked for the split check the formula and PBIX revised:

 

Word List = 
VAR SplitByCharacter = ","
VAR Table0 =
    ADDCOLUMNS (
        GENERATE (
            Unpivot,
            VAR TokenCount =
                PATHLENGTH ( SUBSTITUTE ( Unpivot[name], SplitByCharacter, "|" ) )
            RETURN
                GENERATESERIES ( 1, TokenCount )
        ),
        "NameSplit", PATHITEM ( SUBSTITUTE ( Unpivot[name], SplitByCharacter, "|" ), [Value] ),
        "Numberofwords",
            LEN ( Unpivot[name] ) - LEN ( SUBSTITUTE ( Unpivot[name], ",", "" ) ) + 1
    )
RETURN
  SELECTCOLUMNS(  Table0, "AMount", Divide(Unpivot[amount], [Numberofwords]), "Name", [NameSplit])