For Loop Using DAX Over String

[poweringthru]

I've been searching endlessly for this to no avail.

I have the following table (can't parse result in Power Query because it is a calculated column):

result	itemsNum	totalAmount
"id":"301","amount":"10"|"id":"764","amount":"15"	2
"id":"455","amount":"25"|"id":"908","amount":"75"|"id":"321","amount":"55"|"id":"877","amount":"75"|"id":"989","amount":"60"	5
"id":"112","amount":"65"|"id":"115","amount":"90"|"id":"119","amount":"80"	3
"id":"612","amount":"70"|"id":"729","amount":"25"|"id":"854","amount":"15"|"id":"098","amount":"25"|"id":"075","amount":"30"|"id":"181","amount":"30"|"id":"193","amount":"35"|"id":"303","amount":"40"	8

result string form is "id":"[id_value]","amount":"[amount_value]"|"id":"[id_value]","amount":"[amount_value]"|...

What should amountTotal column formula be if I want to sum each individual row item amount?

[Greg_Deckler · Accepted solution]

@poweringthru Try:

totalAmount = 
    VAR __Path = [result]
    VAR __Table0 = 
        ADDCOLUMNS(
            GENERATESERIES( 1, [itemsNum], 1 ),
            "__Item", PATHITEM( __Path, [Value] )
        )
    VAR __Table1 = 
        ADDCOLUMNS(
            __Table0,
            "__SubPath", SUBSTITUTE( [__Item], """,""", "|" )
        )
    VAR __Table2 = 
        ADDCOLUMNS(
            __Table1,
            "__SubItem", PATHITEM( [__SubPath], 2 )
        )
    VAR __Table3 = 
        ADDCOLUMNS(
            __Table2,
            "__Value", SUBSTITUTE( SUBSTITUTE( [__SubItem], "amount"":""", "" ), """", "" ) + 0
        )
    VAR __Result = SUMX( __Table3, [__Value] )
RETURN
    __Result

I humbly ask forgiveness from the DAX gods...

[poweringthru]

It works perfectly, thank you very much!