Forum Discussion
Create Hierarchy column using Path function on persons data
Hi satishorre20 ,
This issue often occurs when a date filter such as 2025-04-15 is applied. Instead of returning only the records valid on that date, the model processes the entire history, which can result in multiple supervisor IDs per person and disrupt the expected behavior of visuals or hierarchy logic.
To address this, I revised the logic as follows
- The table is initially filtered to include only the rows valid for the selected date (where [Start Date] <= selected date <= [End Date])
- This approach ensures that each ID has only one active supervisor in the current context
- The PATH() function is executed after scoping the data, resulting in accurate and clean hierarchies
- Because the process is controlled by the slicer, it updates automatically with any date change
This results in a dynamic model that accurately reflects the reporting date, eliminates ambiguity, and ensures that visuals such as matrices or trees function correctly, without the need for flattening or static solutions.
The PBIX file is attached for your reference. Please let me know if you need any adjustments in the future.
Regards,
Yugandhar.
Hi satishorre20
Unfortunately PATH needs a 1 to 1 relationship.
When working with historical data, you'll need to use a flattened table that only shows the most up to date result. Try this:
CurrentPersons =
VAR LatestDatePerID =
ADDCOLUMNS (
SUMMARIZE ( 'Persons Data', 'Persons Data'[id] ),
"LatestDate", CALCULATE ( MAX ( 'Persons Data'[Start Date] ) )
)
RETURN
FILTER (
'Persons Data',
'Persons Data'[Start Date] = LOOKUPVALUE (
LatestDatePerID[LatestDate],
LatestDatePerID[id], 'Persons Data'[id]
)
)Then use PATH
Path = PATH('CurrentPersons'[id], 'CurrentPersons'[supervisor id])
Please give a thumbs up and mark as solved if this helps, thanks
Thank you wardy912 and FBergamaschi for the quick response.
I am loooking for to create generic measure/calcualted column to support common semantic model on Type 2 Dimesnion instead flatten the table with the latest record. Id will be a unique for a given date
I was able to create a measure using the below logic successfully, but it is failing in the report when it ran for a specific day.
path2 =
var maxdt = MAX('Calendar Date'[Date])
return CALCULATE( PATH('Persons Data'[id], 'Persons Data'[supervisor id]) , 'Persons Data'[Start Date] <= maxdt && maxdt <= 'Persons Data'[End Date])
The issue : even when applying a date filter (e.g., '"2025-04-15" between 'start date' and 'end date'), the calculation still scans the entire table instead of respecting the filtered subset.