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:

nameamount
a500
b500
c,d500
e,f,g600
h1000
I,j,k,l1000

 

The output I want:

nameamount
a500
b500
c250
d250
e200
f200
g200
h1000
i250
j250
k250
l250

 

Here simple,

I am splitting the amount by the number of values I can find in the name column.

I can only use DAX as my dataset is a Calculated Table. Please help.

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])

     

     

3 Replies

  • MFelix's avatar
    MFelix
    Super User
    4 years ago

    Hi Devesh ,

     

    Using this post you can do the following calculation:

     

    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] )
    )
RETURN
  SELECTCOLUMNS(  Table0, "AMount", Unpivot[amount], "Name", [NameSplit])

     

     

    PBIX file attach.

     

     

    • 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])