Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
97 | |
80 | |
69 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |