Grouping Values into Multiple Categories

[hoyt22]

Hi all,

I'm running into issues when trying to group values into multiple categories. Example below. Smith Corp should be included in two different categories.

Company	Category
Smith Corp	General
Smith Corp	Business
Johnson Inc	General
Jane Co	Business

Ultimately, I would want a Category slicer and a Company slicer. The Category slicer would filter the Company slicer. This would allow the user to look at the Category as a whole or drill down to a specific advertiser within that Category.

Because this isn't a one-to-one relationship or one-to-many relationship with my fact table, I created a mapping table and a bridge table to reduce ambiguity. However, when I add the Category into a slicer, it doesn't filter anything.

I'm not sure if this is how Power BI operates or if I'm missing something in my setup. Any advice would be greatly appreciated!

Test Dashboard on Google Drive

[danextian]

Hi @hoyt22 

You category slicer isn't working because the relationship flows from advertiser bridge to advertiser group mapping only. You can change the relationship to bi-directional to make it work. But for simplicity's sake, I'll use a many-to-many single direction relationship between advertiser group mapping and competitor spend. Test in DAX studio which one is more optimize for your use case.

[hoyt22]

Thanks, @danextian.

So, do I not need the bridge table at all? And for the mapping, does it have to be a many to many relationship or would a one to many work? I'll definitely test it, but I'm trying to work out the logic.

I appreciate your help!

[danextian · Accepted solution]

If you use many-to-many, bridge table isn't necessary. Ensure that the relationship is single direction. 

[hoyt22]

@danextian Thank you! Your solution worked. I've always been wary of many to many relationships, but it seems like it should be fairly straightforward in my case and not result in any issues.