Measure to filter table by multiple related tables using "OR" condition

[wcarter]

I have a table of records which is connected to multiple other tables of demographic data. I need to filter the first table to those records which match the filtered records in any of each of the other tables. For example:

Table 1 is

PersonA

PersonB

PersonC

Table 2 is

PersonA	Red
PersonB	Blue
PersonC	Green

Table 3 is

PersonA	Warm
PersonB	Cold
PersonC	Hot

Table 4 is

PersonA	Happy
PersonB	Bored
PersonC	Curious

In this scenario, if a person is "Happy" OR "Hot" OR "Green" then Table 1 should be filtered to those matching records. I then need to apply this filter to multiple visuals, where the measure returns 1 if a record in Table1 matches and returns 0 if a record in Table1 does not match, while the visual-level filter is set to only show values where the measure returns 1.

This is where I'm at right now, the problem is I'm not sure how to actually compare the values in the visuals (which may be a sum/count of records per higher-level category such as Location or Demographic Group) with the values in my calculated single-column table:

Student_Unduplicated_Filter =

    VAR UPP = DISTINCT(union(

    CALCULATETABLE(SELECTCOLUMNS(Student_PrimaryKeys,"ID",Student_PrimaryKeys[ID]),FILTER(Student_LanguageFluency, Student_LanguageFluency[LF] = 3)),

    CALCULATETABLE(SELECTCOLUMNS(Student_PrimaryKeys,"ID",Student_PrimaryKeys[ID]),FILTER(Student_Programs, Student_Programs[Program] = "Homeless Program" || Student_Programs[Program] = "Foster Youth Program")),

    CALCULATETABLE(SELECTCOLUMNS(Student_PrimaryKeys,"ID",Student_PrimaryKeys[ID]),FILTER(Student_FreeReduced, Student_FreeReduced[SED] = "Yes"))))

    RETURN

    IF(

    VALUE(Student_PrimaryKeys[ID]) IN

    UPP

    ,1,0)

Any help would be greatly appreciated, thank you!

[v-bofeng-msft]

Hi @wcarter ,

I’ve made a test for your reference:
1\My Tables:

Table 1

Table 2

Table 3

Table 4

2\Measure

Student_Unduplicated_Filter =

COUNTROWS(Filter('Table 2','Table 2'[Program] IN {"Happy","Hot","Green"} && 'Table 2'[Person]=SELECTEDVALUE('Table 1'[Person])))>0 ||

COUNTROWS(Filter('Table 3','Table 3'[Program] IN {"Happy","Hot","Green"} && 'Table 3'[Person]=SELECTEDVALUE('Table 1'[Person])))>0 ||

COUNTROWS(Filter('Table 4','Table 4'[Program] IN {"Happy","Hot","Green"} && 'Table 4'[Person]=SELECTEDVALUE('Table 1'[Person])))>0

3\Result

Best Regards,

Bof

[lbendlin]

I have a table of records which is connected to multiple other tables of demographic data. I need to filter the first table to those records which match the filtered records in any of each of the other tables.

All filters are applied by default on top of each other ("AND").  What you want is an "OR" filter - that can only be achieved when you do not link the tables. 

Use measures to implement your logic. Use TREATAS to project your filters, or IN for smaller issues.

Show = if ( SELECTEDVALUE('Table 1'[Person]) IN UNION(values('Table 2'[Person]),values('Table 3'[Person]),VALUES('Table 4'[Person])),1,0)

Use that measure as a visual filter

[wcarter]

Interesting! Closer I think to what i need, but what if I have a grouping category for my Table1 list?

If Table 1 looks like this:

Person1	Human
Person2	Human
Person3	Human

And I want to get a count of all "Human" who match the conditions of the filter, when I update the file you shared there are no matches and the visual is blank.

Thank you!

[lbendlin]

You would need to materialize the measure before you can do your count.

[wcarter]

That's what I was afraid of, if you can see any way to get the filter to work the way I need please let me know, until then I'll have to create a calculated table and filter using that.

[lbendlin]

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.