Filter Based of Filter Table (List of Yes/No That Can Be Check, Each Different Column of Start Table

[lancekam]

Hello,

I have 19 columns that are Yes/No Columns in my original table, that I want to be able to slice on each.  I did not want each to be it's own slicer, so I created a "Filter Table", a table that can be put into a Hieracy Slicer, so anyone can expand which column they want to select.  I have it set up where it will display a card, showing which columns are sliced, and if both "Yes" and "No" are selected from the same column, it ignores it (don't ask).  I can also easily get is "Yes" or "No" selected for each category.  Where I am stuck is, without my Calculate statement being gigantic, is it possible to Filter my original Table based off what is selected from my new Filter Table?

So, for example.  Say I have "A", "B", and "C" as Yes/No options.  If the following is selected:

I want to perform all of my measures for any location where "B" is "YES" and "C" is "NO".
Here is a link to an example PBIX.

FilterEX Google Drive PBIX 

[Anonymous · Accepted solution]

Hi @lancekam ,

I have used your measure and duplicate it for A and C as well:

A Filter = 
IF(SEARCH("A", [CatFilters], 1, 0) <> 0, IF(SEARCH("Is A", [CatFilters], 1,0) <> 0 && SELECTEDVALUE(Sheet1[Is A]) = "Yes", 1, IF(SEARCH("Is NOT A", [CatFilters], 1, 0) <> 0 && SELECTEDVALUE(Sheet1[Is A]) = "No", 1,0)),1)

B Filter = 
IF(SEARCH("B", [CatFilters], 1, 0) <> 0, IF(SEARCH("Is B", [CatFilters], 1,0) <> 0 && SELECTEDVALUE(Sheet1[Is B]) = "Yes", 1, IF(SEARCH("Is NOT B", [CatFilters], 1, 0) <> 0 && SELECTEDVALUE(Sheet1[Is B]) = "No", 1,0)),1)

C Filter = 
IF(SEARCH("C", [CatFilters], 1, 0) <> 0, IF(SEARCH("Is C", [CatFilters], 1,0) <> 0 && SELECTEDVALUE(Sheet1[Is X]) = "Yes", 1, IF(SEARCH("Is NOT C", [CatFilters], 1, 0) <> 0 && SELECTEDVALUE(Sheet1[Is X]) = "No", 1,0)),1)

And then apply them to filter on visual pane, set as " is 1":

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

[lancekam]

Well, I came up with a solution that seems to work.

So I already had the measure that listed the filters turned on.  I just used an if with a Search and SelectedValue to get it to filter.
From my example, the code ends up looking like:

B Filter =

IF(SEARCH("B", [CatFilters], 1, 0) <> 0, IF(SEARCH("Is B", [CatFilters], 1,0) <> 0 && SELECTEDVALUE(Sheet1[Is B]) = "Yes", 1, IF(SEARCH("Is NOT B", [CatFilters], 1, 0) <> 0 && SELECTEDVALUE(Sheet1[Is B]) = "No", 1,0)),1)

Well, this works if I am just using the Base table, but if I try any related tables and try to do counts/sums, it fails.  So back to looking for suggestions.