Hi
I have a list of properties/addresses. These work within a hierarchy, a Property is within a Sub-block is within a Block is within an Estate. Every level of the hierarchy doesn’t always exist but that is the order, for example, a Property could just be within the Block and there is no Sub-block.
I have a table for the properties with columns for the relevant sub-blocks, blocks, estates and the references for each level of the hierarchy.
I have another table which with a list of different water assets and a number of columns, Asset Code, Asset Type, Asset Code. These water assets each correspond to either a property, sub-block, etc. There could be a corresponding asset at any level of the hierarchy.
I have created a list made from the original property list with each different hierarchy level appended on, the is then fed into a slicer. So you can select any level of the hierarchy from one list. The list has a reference column.
The water asset table also has a reference column which corresponds to the relevant reference numbers in the property list.
I would like it so that when the slicer is selected, it returns the results of every level of the hierarchy in a table visual.
Here is an example file, https://we.tl/t-K8VdmmTLbw, in this file is a hierarchy
Property - 11 High Street
Sub-block – 11-40 High Street
Block – High Street
Estate – High Street Estate
If you select any of those addresses in the slicer I want it to return the same results as they within the same hierarchy. It should return any corresponding results from the water asset table for any of those addresses.
I hope that makes sense. I thought I might be able to do this with a large amount of merges but from previous experience this would grind power query to a halt with the amount of data I’m looking at. So how can I do this more efficiently?
Thank you in advance for any help.
