Slicer for overlapping groups - need help

[McSarah]

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?

LookupGroupFilter= IF(

HASONEVALUE(LookupGroup[LookupGroupID])

    ,SWITCH(

        MIN(DataTable[ETOTableGroupID])

       ,1,IF(DataTable[Flag1] = TRUE(),TRUE(),FALSE())

       ,2,IF(DataTable[Flag2] = TRUE(),TRUE(),FALSE())

       ,3,IF(DataTable[Flag3] = TRUE(),TRUE(),FALSE())

       ,4,IF(DataTable[Flag4] = TRUE(),TRUE(),FALSE())
       )

    ,FALSE()

)

Thanks!

[Anonymous]

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.

Unpivot columns (Power Query) 

Regards,

Xiaoxin Sheng

[McSarah]

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.

[Anonymous]

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

[McSarah]

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.