Forum Discussion
Transform table using DAX
Hi Anonymous ,
I'm already abble to calculate the values on the Sum level based on the parent and child relation:
Sum Level =
VAR PathValue =
PATH ( 'Table'[PlanTemplaRowID], 'Table'[PlanTemplateParentRowID] )
VAR Pathlengthvalue =
PATHLENGTH ( PathValue )
RETURN
SWITCH ( Pathlengthvalue, 1, 3, 2, 2, 3, 1 )
Only question that is remaining is the sorting, can it be done based on the seuquence number or not?Is the Sequence Nº is not a part of your original table? Asking this because on the first calculations I have send you refered that they could not be used.
Thanks for this answer. It is fine to idé SequenceNo as long as the solution take into consideration that the values may change. I added a support column which I thought could help to understand ☺️
Hi Anonymous ,
When you refer that the SequenceNo can change do you mean that it will change but you want the sorting to be the same? so in this case if for example EBIT changes from 0 to 55 you would still want it to keep it on the order 11?
Or does it mean a different thing?
This questions come from the fact that to make a measure to calculate this order we need to have the logic similar. Altough I'm looking at the example you gave the only logic I see is only the sequence number however you say this can change, and on the rest of the values I do not see any logic of order being it alphabetic or number.
Once again sorry for all the questions but want to give you a solution that is scalabble and flexible.
The sequenceNo may change and then the sorting should reflect that change.
When you reflect that should reflect is to keep it on the same sorting ?
Again EBIT row that has a sequence of 0 if it changes to 55 should keep the sorting order of 11 correct?