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.
Hi everyone, I'm new to PowerBI so bear with me:
I need a slicer that can be used to filter a view down to the members of a given group or groups. A record/ member can be in one or many groups, where group membership is defined by a series of true/false flags on the main table. (Which means a simple FK to a lookup table will not work.)
I'd like a single slicer control to drive this behavior, not multiple individual slicers. And I'd like to avoid having to build out a many-many association table, since this data is subject to change and I want to keep things simple (plus this is a common use case in my organization and I want a lean solution). I've done this many times in Tableau, but I'm having trouble figuring out how it's done in PowerBI.
Here's the structure of my primary table:
name | group1 | group2 | group3 | group4 | and so on....
apple TRUE FALSE FALSE FALSE
banana FALSE TRUE TRUE FALSE
peach TRUE FALSE TRUE FALSE
pear TRUE FALSE FALSE TRUE
plum FALSE FALSE FALSE FALSE
And here's my current setup, which is failing:
1) a data table with flags in the view, like the above example. I'm displaying the row level table in the report, not an aggregation.
2) an unlinked lookup table to provide values for the slicer & to be used in calc field logic (GroupID, GroupName)
3) a slicer built from the lookup table (list of GroupNames, single select for now but multiselect is ideal)
4) a calculated field on the primary data table that tries, and fails, to filter the data table according to the option selected in the slicer
And here's my formula, which does not work. Even if it did, it would not be quite what I want since it would only allow selection of one group at a time. The idea is that, depending on the value selected in the slicer, the TRUE/FALSE values in the selected flag column would pass over to the measure column, and then the view would be filtered to CalcField=TRUE as a filter setting in the filter pane. However, the values do not seem to pass to the measure column as expected. What should I be doing differently?
Hi @McSarah ,
I'd like to suggest you 'unpivot columns' on your category fields, then you can write measure formula to simply looping these fields and compare with tags.
Regards,
Xiaoxin Sheng
Thank you Xiaoxin. Can you tell me: could the series of operations necessary to unpivot dynamically adjust to future changes or additions to the series of tags used? I assumed it would not and I anticipate future changes to the list. This is why I hoped to handle this case with a formula.
Otherwise, yes, your solution would work and I may end up there anyway.
Hi @McSarah,
In power query, 'unpivot column' function only requires you to set the category column and unpivot two column names.
So if your category column does not been modified, you only need to refresh your source table and these new group fields will also append into unpivot columns.
Notice: I remove the auto-generated 'change type' step(after PromoteHeaders step) to prevent 'key not match' issue that caused with power query 'cache previous data structure' mechanism and manually do it after 'unpivot columns' steps.
Regards,
Xiaoxin Sheng
I expect to need to modify the cateogories, add additional categories, etc. This is the reason I wanted to avoid unpivot. I think that unpivot is likely not the best solution since I think it will require more rework than I want to do.
Since I need the categories and groupings to be more flexible, I think I will need to stick with standard groupings and sacrifice overlapping categories until the categories are more fixed.
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 |
---|---|
114 | |
98 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |