Filtering Table by Slicer Selection and Heirarchy

[geo_lan]

Hello,

I am struggling with applying a slicer filter to a table visual, which involves use of an approval heirarchy. After spending a lot of time trying to figure this out and researching for a solution, I think it's now time to reach out for assistance.

Context

I am building an ordering approval visual, so when a user selects a cost centre (in a slicer) to see who can approve for it, the table visual is filtered not only by that specific cost centre, but by any parent cost centre that sits above that cost centre in the role_authorities table.

This is the page I am building:

1 = The table visual of results which displays field roh_cost_centre (column "cost_centre" in picture)

2 = The slicer where a user can select from field roh_cost_centre

This is an example extract of the cost centre heirarchy data table (table name  = role_authorities):

So the result I am looking for, is if the user selects "1010" in the slicer, then the table visual is filtered by roh_cost_centre 1010    AND   1A / CMTMED / ACEM     as these cost centres are parents as per the role_authorities table.

So far, I have tested using a measure (below) to try and filter on any rows returning value "1", but only rows with the actual "1010" selection returned value "1" (I know there are users that have roh_cost_centre 1A and CMTMED populated so these should be visible too):

Hopefully that makes sense. Any help please to get the table visual to filter by the additional parent cost centres too is greatly appreciated, thank you.

[v-xiaotang]

Hi @geo_lan 

Thanks for reaching out to us.

please share a sample file that fully covers your issue, thanks.

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[geo_lan]

Hi v-xiaotang,

Please see below link to example pbix file. You will see I have attempted 2 different filters in 2 tables, but not successful with either. Frustrating as I can create the desired result in excel very easily with Index formula.

example_filter_report 

Hopefully you can access ok, but if not please let me know. Thank you.