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. Records table - contains 2 columns: record ID and selected values that are comma separated

2. Max score table - contains 2 columns: selection and its corresponding score

 

What I am trying to achieve is to create a new column called total max score on the records table, whose values will depend on what selections are in that particular record. For example, if record ID001 has the selection A,B and A has score of 300, and B has a score of 100, the total max score for ID001 will be 400.

 

Unfortunately, with my still limited knowledge in DAX or Power Query, I am not able to provide any code so far, and hoping someone can provide some hints on how I can do this.

 

Records Table

Record IDSelected
ID001A,B
ID002A
ID003A,C

 

Max Scores Table

 

SelectionsScore
A300
B100
C500

 

Desired output for Records Table:

 

Record IDSelectedMax Score
ID001A,B400
ID002A300
ID003A,C800

 

Thanks in advance for any help!

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

     

     

     

3 Replies

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

     

     

     

  • 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.

  • 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!