Create a slicer that checks multiple columns for a value

[Semipro211]

Good Morning. I have done some seraching over the past few weeks and I have not been able to find anything that works, so apologies if this has been answered before. I am trying to find a way to filter a table using a slicer that looks at multiple column values concurrently. A brief scenario of the data is as follows:

Table 1:

Address Code

A12345

B34562

C65788

Table 2:

Item #	Addr One	Addr Two	Addr Three
1245785421	A12345	B34562	C65788
1234567890	A99001	A12345	D65478
9875642135	B34562	E78541	A12345
1125896325	A99001	E78541	C65788
5419654782	C12345	D12345	E12345

Given the above, what I am trying to achieve is a slicer so that if I pick an address code from Table 1, It will show the rows of all item numbers where the Address Code matches the value in any of the 3 Addr columns from Table 2. For example, Item # 1245785421 would show in the visual whether I selected A12345, B34562, or C65788 because all those values have a match in one of the 3 columns. However, Item # 5419654782 would only display when C12345 is selected in the slicer.

The only way I have been able to get this result is to create a derived table using CALCULATETABLE(SUMMARIZE(Table 2, Item #), FILTER(Table 2, CONTAINSSTRING(Addr One, "A12345") || CONTAINSSTRING(Addr Two, "A12345") || CONTAINSSTRING(Addr Three, "A12345").

I would have to repeat that process for every Address Code in Table 1 then relate each of them back to both Table 1 and Table 2. While this works, it feels inefficiant and the report performance is very slow due to the fact that the actual tables have many more columns and rows. Is there a way to achieve what I am trying to do without the use of the calculated tables? Oh, and the reason I used CONTAINSSTRING is because the real data has many more characters and will almost never be exactly the same as a listed Address Code.

Any help is very much appreciated!

[Semipro211 · Accepted solution]

Just wanted to let everyone know I was finally able to find a working solution for this. What was needed was I created a slicer table via

Slicer = 
DISTINCT(
    UNION(
          VALUES(Table 2[Addr One]),
          VALUES(Table 2[Addr Two]),
          VALUES(Table 2[Addr Three])
         )
        )

Then, I created a measure with the following:

SlicerMeasure = IF(
                        MIN(Table 2[Addr One]) IN VALUES(Slicer[Addr]) || MIN(Table 2[Addr Two]) IN VALUES(Slicer[Addr]) || MIN(Table 2[Addr One]) IN VALUES(Slicer[Addr]), 1, BLANK())

By adding that measure to my table visual, I am now able to use the single value to look at all 3 columns and return the correct result set. Thanks goes out to @v-jiascu-msft who posted this as an answer to another question here. The only tweak I had to make was I DID create a relationship from my Slicer table to my Table 1 because I did not want values that were not in Table 1 to display in the slicer options. In testing, this now works perfectly!