PATH function with CALCULATE

[Anonymous]

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. 

[Anonymous]

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)

[Anonymous]

Hi @Anonymous ,

Could you tell me your logic in the red line part? I think the PATH function may not be applicable here.

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.

[Anonymous]

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.