Data Modelling - slicer for asset tree between two related tables

[Anonymous]

I have two data sources - 'Dents' and 'Events' - which are related by EventID. They are also both tied to the same 'Asset Tree' which I want to use to slice the data

I think the key thing giving me dificulty is that not all events have dents and not all dents have events. There can be many events per dent, but only 1 dent per event. I would like to be able to use a slicer based on Asset Tree level and get all results for both tables but I currently don't have a relationship in my model because I'm not sure how to set it up. I think the biggest problem is that DentAsset can be at any level in the asset tree. 

I want to be able to filter by ChildAsset=L2 and see all dents where DentAsset=L or L2 even if they have no related events. I also want to see all events where ChildAsset =L2  so my results would look like this:

Any advice woudl be greatly appreciated! Would be happy to share my report if that's helpful. 

[NaveenGandhi · Accepted solution]

Hi @Anonymous 

I have built a model and attached as PBIX. Let me know if it helps.

If you have any issues or questions, Please let me know.

If this post helps, then please consider Accept it as the solution to help the others find it more quickly. Appreciate you kudos!!

Follow me on LinkedIn!!1