Forum Discussion
PATH function with CALCULATE
Hi,
I have a hierarchy table with column "ID" and "Parent ID". In the same table, I have this hierarchy for each week, so depending the week, the "Parent ID" may changes.
| ID | Parent ID | Week | Path |
| 1 | 2 | 1 | 3|2|1 |
| 1 | 2 | 2 | 2|1 |
| 1 | 3 | 1 | |
| 2 | 3 | 1 | 3|2 |
| 2 | 2 | 2 | |
| 2 | 3 | 2 | |
| 3 | 1 | 3 | |
| 3 | 2 | 3 | |
| 3 | 3 | 3 |
So the Path depends of the week.
I tried this :
Path =
VAR Monitored_Week = 'Table'[Week]
RETURN
CALCULATE(
PATH('Table'[ID],'Table'[Parent ID])
,FILTER('Table' ,'Table'[Week] = Monitored_Week))
But I have this issue :
Each value in 'Table'[ID] must have the same value in 'Table'[Parent ID]. The value '1' has multiple values.
I do not understand because inside the CALCULATE function, the Table is filtered only for the row with the same Week value.
3 Replies
Hi Anonymous ,
PATH(<ID_columnName>, <parent_columnName>)<ID_columnName> contains the unique identifier of the row in the table. The ID column cannot have duplicate values.
Path = PATH([ID],[Parent ID])Reference: PATH function(DAX)
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank Anonymous
I understand the fact that the <ID_columnName> contains the unique identifier of the row in the table. That is why I want use the CALCULATE function in order to "Evaluates an expression in a modified filter context."
Reference : CALCULATE function(DAX)