Create Hierarchy column using Path function on persons data

[satishorre20]

Good morning,

I am trying to create a column using "path" dax function on id and supervisor id on “Persons data” and persons data is Type -2 Dimension table. id will be natural key and may have multiple rows when any change in other column for the given id.

I am getting the error while creating a column using the below logic

path = PATH('Persons Data'[id], 'Persons Data'[supervisor id])

Error message:  Each value in 'Persons Data[id]’ must have the same value in 'Persons Data[Supervisor id]'. The value '1' has multiple values

Can you please share your thoughts when you get a chance. Thank you.

Sample Data:

[V-yubandi-msft]

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

1. The table is initially filtered to include only the rows valid for the selected date (where [Start Date] <= selected date <= [End Date])
2. This approach ensures that each ID has only one active supervisor in the current context
3. The PATH() function is executed after scoping the data, resulting in accurate and clean hierarchies
4. 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.

[FBergamaschi]

Hi,

you have the same ID for different people? In that case you need to create a unique key, otherwise you are getting an error that says that it seems that the same person has different supervisors. Maybe you can concatenate the ID with the start date and try on that column

Please give kudos if this helped and/or mark it as a solution if it solved

[wardy912]

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

[satishorre20]

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.