cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Helper I

## For Loop Using DAX Over String

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?

1 ACCEPTED SOLUTION
Super User

@poweringthru Try:

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

I humbly ask forgiveness from the DAX gods...

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
2 REPLIES 2
Super User

@poweringthru Try:

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

I humbly ask forgiveness from the DAX gods...

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Helper I

It works perfectly, thank you very much!

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors