cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Can't get SUMX on GENERATESERIES to work

Hello,

I simply want the DUPLICATE_INDEX to represent how many of the numbers should be added together from PLANNING. This can't be hardcoded, the length of PLANNING can be 100+ digits and go from 1-999. If theres a better way feel free.

Current Output
DUPLICATE_INDEX, PLANNING, OUTPUT
1, "1,3,1", 131
2, "1,3,1", 131
3, "1,3,1", 131

Expected Output
DUPLICATE_INDEX, PLANNING, OUTPUT
1, "1,3,1", 1                  (1)
2, "1,3,1", 4                  (1+3)
3, "1,3,1", 5                  (1+3+1)

Code:

VAR DigitsTable =
GENERATESERIES(1, PATHLENGTH([PLANNING])),
"Digit",
VALUE(PATHITEM([PLANNING], [Value]))
)
RETURN SUMX(FILTER(DigitsTable,
[Value] <= [DUPLICATE_INDEX]
),
[Digit]
)

Many Thanks,

Bailey.

1 ACCEPTED SOLUTION
Frequent Visitor

Update : I used PowerQuery to get the same result. The code may not be useful, but it did the job.

let
count = if [PLANNING] = "" then 0 else List.Count(Text.PositionOf([PLANNING], ",", Occurrence.All)) + 1,
dup_index = List.Transform({1..count}, each _)
in
dup_index
)

3 REPLIES 3
Super User

@BaileyL , Try using below DAX

VAR DigitsTable =
GENERATESERIES(1, PATHLENGTH([PLANNING])),
"Digit",
VALUE(PATHITEM([PLANNING], [Value]))
)
RETURN
SUMX(
FILTER(
DigitsTable,
[Value] <= [DUPLICATE_INDEX]
),
[Digit]
)

 Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!

Frequent Visitor

Update : I used PowerQuery to get the same result. The code may not be useful, but it did the job.

let
count = if [PLANNING] = "" then 0 else List.Count(Text.PositionOf([PLANNING], ",", Occurrence.All)) + 1,
dup_index = List.Transform({1..count}, each _)
in
dup_index
)

Frequent Visitor

Hi,

This doesn't seem to work. The output is still 131. Thank you though.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors