TableA
| Submission ID | Submission State |
| 1 | IL |
| 2 | IL;NC |
| 3 | NC |
| 4 | ND;NC;IL |
TableB
| Policy ID | Policy State |
| 1 | IL |
| 2 | NC |
| 3 | ND |
| 4 | IL |
| 5 | IL |
| 6 | IL |
| 7 | IL |
| 8 | IL |
TableC
| State Abbreviations |
| NC |
| ND |
| IL |
I have created a visual that displays the Distinct Count of Submission ID from TableA and a separate visual that displays the Distinct Count of Policy ID from TableB. That is easy enough, but I also need to allow the user to filter both viz's by State. In TableA, however, Submission State is stored in the following manner, for example:
"ND;NC;IL"
TableB stores Policy State individually as expected with one State per row. I created an additional table, TableC, that contains all State Abbreviations. TableC.State Abbreviation is used as my slicer on the page. I would like both visuals to filter based on the slicer selection. The first table would need to filter based on some sort of CONTAINS function. For example, if the user selects "IL" in the slicer, they would get a value of 3 from the first visual and 6 for the second visual. I created a calc as follows:
State Selected = CONTAINSSTRING('TableA'[Submission State],SELECTEDVALUE('TableC'[State Abbreviation]))
I added State Selected as a filter on my first visual and set it to True. However, when I select a State from the slicer, nothing happens to the value displayed in the top visual. It continues to show the default value of 4 (Distinct Count of Submission ID).
My plan was to apply a similar filter to the second visual but can't get the first one working. Any ideas?
