Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Sum Comma seperated Values using a calculated column

Dear All,   I have a validated Data Model to which i can bring only little change to it. No major Power Query or Data modeling possible. I have imported through the model a column containing integ...
Need Help
parry2k's avatar
parry2k
Icon for Super User rankSuper User
6 years ago

Anonymous here is the measure that will work

 

Measure = 
VAR col = MAX ( 'Table'[Column1] )
VAR mymeasure =
    SUBSTITUTE ( col, ",", "|" )
VAR Mylen =
    PATHLENGTH ( mymeasure )
VAR mytable =
    ADDCOLUMNS (
        GENERATESERIES ( 1, mylen ),
        "mylist", VALUE ( PATHITEM ( mymeasure, [Value] ) )
    )

RETURN
    SUMX ( mytable, VALUE ( [mylist] ) )

 

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    parry2k 

     

    Many thanks for the proposed approach, exactly what I was looking for. Even not being as advanced user as you are, I understand the logic you proposed.

    few comments: 

    I need this to work in a row context environment in order to assess for every row the sum of each rejected quantities.

    >> if I plug this DAX in a calculated Column to be assessed in a row context, I get always the same value.

     

    can we get to the desire result? (Note the Table'[Column1] may contain blank values if important to know)

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Anonymous 

      Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details your problem or share me with your pbix file from your Onedrive for Business.

       

      Best Regards,

      Rico Zhou