Creating calculated column based on comma separated values on a column

[Anonymous]

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 ID	Selected
ID001	A,B
ID002	A
ID003	A,C

Max Scores Table

Selections	Score
A	300
B	100
C	500

Desired output for Records Table:

Record ID	Selected	Max Score
ID001	A,B	400
ID002	A	300
ID003	A,C	800

Thanks in advance for any help!

[Anonymous]

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!

[v-chenwuz-msft]

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.

[MFelix · Accepted solution]

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