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

     

     

     

MFelix's avatar
MFelix
Super User
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])