Calculating costs per Path on hierarchical data

[veikko]

Hello,

I have a million rows of hierarchical data which looks like this:

ID	Path	Level	Cost	PathCost	Code
1	1	0	10	120
2	1|2	1	15	110	XXX
3	1|2|3	2	20	95
4	1|2|3|4	3	10	25
5	1|2|3|4|5	4	5	5
6	1|2|3|4|6	4	10	10	YYY
7	1|2|3|7	3	20	50
8	1|2|3|7|8	4	15	20
9	1|2|3|7|8|9	5	5	5	ZZZ
10	1|2|3|7|10	4	10	10

PathCost is a calculated column which calculates the costs for every row. Costs are cumulated from every row that contain row's path. This is the code for the PathCost column:

PathCost =

    VAR Id_ = [Id]

    RETURN

        SUMX (

            FILTER (

                COSTS,

                FIND(Id_, COSTS[Path], 1, BLANK()) > 0

            ),

            [Cost]

        )

The problem is that the calculated column is very slow. I narrowed the data to 10,000 rows and it loaded the dax for over 15 minutes. Do you have any suggestions how the code could be optimized? I do not need the costs for every row, only the rows where column Code has a value.

I also tried finding paths inside paths instead of finding id's inside paths. It was not significantly faster.

[v-tangjie-msft]

Hi @veikko ,

Please try using a measure instead of calculated column and then output them as tabular visuals.

athCost =
    VAR Id_ = max([Id])
    RETURN
        SUMX (
            FILTER (
                all(COSTS),
                FIND(Id_, COSTS[Path], 1, BLANK()) > 0
            ),
            [Cost]
        )

For more information about DAX optimization, you can refer to:

Performance Tuning DAX - Part 1 - Microsoft Power BI Community

DAX Best Practices | MAQ Software Insights

Best Regards,

Neeko Tang

If this post  helps, then please consider  Accept it as the solution  to help the other members find it more quickly.