Forum Discussion
Calculating costs per Path on hierarchical data
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.
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.
1 Reply
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.
