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

     

     

     

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Thanks MFelix and v-chenwuz-msft! The solutions you both provided fulfilled my requirement. There are really different ways to solve a requirement in Power BI, as long as you have a good understanding of the functions. I hope to get there soon!

 

As I only managed to go online today, I will credit the solution to Miguel as his post came first. Thanks heaps for the help guys!