Forum Discussion
Create a slicer that checks multiple columns for a value
- 6 years ago
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!
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!