Forum Discussion
Working with complex node path data in both dimension data and fact data
So, in our data source we have a lot of data stored together with a map to determine parent and child hierarchy. The data is stored in 2 tables: [Event] and [Map_Events] [Event] consists ...
Power Query M:
let
Source = Map_Events,
FlattenHierarchy = List.Generate(
() => Source,
each Table.RowCount(_) > 0,
each Table.Join(_, "child_id", Source, "parent_id", JoinKind.LeftOuter),
each Table.SelectColumns(_, {"parent_id", "child_id"})
)
in
FlattenHierarchy
Load the flattened hierarchy table along with the Event table into Power BI.
Ensure there are proper relationships between these tables. Relationships should be set based on id fields.
DAX Measure
AllDescendants =
VAR SelectedProcess = SELECTEDVALUE(Event[id])
VAR AllRelated =
CALCULATETABLE(
VALUES(FlattenHierarchy[child_id]),
FILTER(
FlattenHierarchy,
FlattenHierarchy[parent_id] = SelectedProcess ||
FlattenHierarchy[child_id] = SelectedProcess
)
)
RETURN
AllRelated
💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn
Thanks Kedar_Pande I will try to implement your suggested solution and give you feedback on how it works out!
Kedar_Pande is the Idea here to create a table using dax (AllDescendants) and link this to the table containing all events?
The table with mappingdata is already flattened, it only contains a list of all parent_id child_id combinations.
