slicer with conditions - if value selected,select related values

[Anonymous]

i have 2 tables

table 1 - list of all codes

 code id,  code text

code id	text
x41	acdt poisn
x42	acdt poisn narc
y11	acdt poisn othr
y12	acdt poisn aaa
i26.0	pulmonary acute
i26.9	pulmonary WO acute
J96.0	acute respiratory
d68.3	disosrder
g21.1	other drug
g24.0	drug induced
y46	adverse effects
y47	tri adverse
y49	ana adverse

table 2 with patient details

patientname, admissiondate, discharge date, codeid,codetext

 (3rd table / new requirement)

3rd table contains 4 columns

diagnosis	code	code text	external codes
pulmonary embolism	i26.0	pulmonary acute
pulmonary embolism	i26.9	pulmonary WO acute
drug related complications	J96.0	acute respiratory	x41,x42,y11,y12
haemorrhagic disorder	d68.3	disosrder
moment disorder	g21.1	other drug	y46,y47,y49
moment disorder	g24.0	drug induced	y46,y47,y49
serious alteration	r40.0	somnolence	y46,y47,y49
serious alteration	r40.1	stupor	y46,y47,y49
serious alteration	r40.2	coma	y46,y47,y49

in slicer when u select a code from table 3

it needs to filter that code and filter other external cause codes as shown in table 3

for example if you select j96.0 it needs to select j96.0 and other external codes x41,x42,y11 and y12

in another scenario if i26.0 is selected then just select all records realted to code i26.0 only(  no associated codes to select)

[v-yuaj-msft · Accepted solution]

Hi @Anonymous ,

Based on your description, you can create a measure as follows.

Measure =

var _id = MAX(Table1[code id])

return

IF(

    OR(

        _id in DISTINCT(Table3[code]),

        SUMX(

            ADDCOLUMNS(

                DISTINCT(Table3[external codes]),

                "Re",

                IF(

                    CONTAINSSTRINGEXACT([external codes],_id),

                    1,0

                )

            ),

            [Re]

        )>0

    ),

    1,0

)

Then drag the measure to “filters on measure” and filter the visual when “measure is 1”.

Result:

Hope that's what you were looking for.

Best Regards,

Yuna

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

[MFelix]

Hi @Anonymous ,

I assume that both tables are unrelated so create the following measure:

filter =
IF (
    SELECTEDVALUE ( 'Codes'[code id] ) IN VALUES ( 'Requirements'[code] )
        || SELECTEDVALUE ( 'Codes'[code id] ) IN VALUES ( 'Requirements'[external codes] );
    "Symptom"
)

Now you can use this column to filter out other visualizations.