use the selectedvalue of a slicer from a disconnect table to filter data in another table and chart

[KC_MS]

new to power bi and need some help~

i have 2 tables, simplified as below

tbl_RE

tbl_hierarchy

what i'm trying to do is to create sth like this 

the left is a slicer base on tbl_hierarchy, middle and right is data table and chart both based on data from tbl_RE
when user pick a value from the slicer (let say Entity 1), the table and the chart should refresh accodingly to include only records related to Entity 1.
Another thing here is "Groupwide" actually means all entities under the group and "ALL APAC" means all entities in APAC, so no matter which entity the user selected, these 2 should be included.

so if user picked Entity 1 from the slicer, the table should refresh to only show RE1, RE3, RE4 and RE5 and the chart should also be refresh to only include show RE1, RE3, RE4 and RE5.

i tried to create a measure to filter the table, which "seems" work on filtering the table but it doesn't work on the chart...

appreciate if anyone could help~~ Thx

P.S. i know it's much better to provide sample workable data and i created a pibx. but my IT guy did a good job to block all the online storage i know... so sorry for the inconvenience

[Anonymous · Accepted solution]

Hi,@KC_MS 
I am glad to help you.

You can refer to my test results below, where I labeled the data by creating a measure and filtered the data by the value of the measure, while the slicer was only used to pass in the filtering criteria, and had no filtering functionality of its own

here is my DAX code:

M_ =

VAR selected_=SELECTCOLUMNS('tbl_hierarchy','tbl_hierarchy'[Name])

VAR selected_oneValue=SELECTEDVALUE('tbl_hierarchy'[Name])

VAR table_value=MAX('tbl_RE'[Impacted_Entities])

VAR result_=

IF(MAX('tbl_RE'[Impacted_Entities])IN selected_ ||MAX('tbl_RE'[Impacted_Entities])="ALL APAC"||MAX('tbl_RE'[Impacted_Entities])="Groupwide",

1,0)

RETURN

SWITCH(TRUE(),

result_=1,1,

CONTAINSSTRING(table_value,selected_oneValue),1,

0

)

Add a filter of M_=1 to both visual "filter" fields.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian,

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

[KC_MS]

thx @Anonymous ~
it works perfect~

if you have some spare time and if you don't mind, can you explain a bit more on your DAX code and how it works?
especially on those "MAX" which i don't quite get what it does here and the purpose?

on a side note, what if i want to allow user to select multiple entities from the slicers?

thanks again for your help~