Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Creating calculated column based on comma separated values on a column

Hi,   Still learning Power BI here, and I have currently encountered one challenge that I can't seem to solve. Hoping someone can enlighten me on how to address this:   I have 2 tables: 1. Recor...
Need Help
  • MFelix's avatar
    MFelix
    4 years ago

    Hi Anonymous ,

     

    You can achieve this column by using the following syntax:

    MAximumScore = 
var RecordIDValue = Records[Record ID]
VAR SplitByCharacter = ","
var table1 =

ADDCOLUMNS (
    ADDCOLUMNS (
        GENERATE (
            Records,
            VAR TokenCount =
                PATHLENGTH ( SUBSTITUTE ( Records[Selected], SplitByCharacter, "|" ) )
            RETURN
                GENERATESERIES ( 1, TokenCount )
        ),
        "Word", PATHITEM ( SUBSTITUTE ( Records[Selected], SplitByCharacter, "|" ), [Value] )
    ),
    "@MAXIMUMSCORE",
        MAXX (
            FILTER ( ALL ( 'MAx Scores' ), 'MAx Scores'[Selections] = [Word] ),
            'MAx Scores'[Score]
        )
)

return
SUMX(FILTER(table1, Records[Record ID] =  RecordIDValue), [@MAXIMUMSCORE])

     

     

     

v-chenwuz-msft's avatar
v-chenwuz-msft
Community Support
4 years ago

Hi @roosechua,

 

There two ways, DAX and power query

DAX expression:

1 select the Selected column in power query editor and split this column by delimiter and click “ok”

 

2 close and apply in the upper left corner.

3 create a new column with this measure

Max Socore =

var _1 = LOOKUPVALUE('Max Scores'[Score],'Max Scores'[Selections],'Records'[Selected.1])

var _2 = LOOKUPVALUE('Max Scores'[Score],'Max Scores'[Selections],'Records'[Selected.2])

RETURN

_1+_2

4 Result:

 

 

Power Query

1 Go to the power query editor and create a new column in the Records table via the Custom Column under Add Column tab.

 

2 Enter the blow formula:

= List.Sum(

    List.ReplaceMatchingItems(

        Text.Split([Selected],","),

        Table.ToRows(#"Max Scores")

    )

)

 

3 The result:

 

 

 

Best Regards

Community Support Team _ chenwu zhu

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.