Filtering across multiple columns, best practices? Many-to-Many or 1-to-Many (dim)

[BryMan_17]

I have a case for something where either many-to-many or a 1-to-many relationship seems like my only options and am seeking advice on best practices and which to use:

My example is user-manager assignments and specific filtering criteria:

I have a table of users that all report up to “Thomas”, some users may have multiple levels of managers in-between them.  My table looks like this:

I also have a fact table of claims, with a unique key, and assignment, among other details.

The ask: For a filter in the filter pane or slicer where if the user selects a name, they see their claim assignments, but also the assignments of everyone any level below them.

Example:  Thomas would see everyone, David sees David, Francis, Jamie, and Mary, Mary only see Mary.

I have only come up with 2 solutions, a many-to-many relation, a bi-directional relationship with the many side of the 1-to-many being the dimension table.

Note: I am aware of a third somewhat solution of using a measure to check for selections and a filter applied to each visual, however, that causes some visuals to time out, so I excluded that.

I have two Many-to-many ideas:

Concatenating and repeating all combinations in both tables.

This seems inefficient, as it adds a column with a lot of characters.

OR

Creating a table that repeats users for all combinations and joining on the analyst name:

1-to-many example:

Repeating claim number and user, and join on claim number:

A drawback to the 1-to-many, is that the dimension table could reach over 10M rows in some models I wish to use this filtering concept.

Natruatlly, avoid both of these things in a data model and use a traditional star schema; however, it seems unavoidable for this ask. 

What method would be considered the best practice?

[ray_aramburo · Accepted solution]

One to many is the better idea, here's another recommendation: if you have a User ID, ensure to have it on the Assignments/Claims table and then relate the Users ID single table containing all the hierarchy with it. That way you'll be able to filter the Claims table by the L3 Manager from your Users Catalog/Hierarchy. 

[ray_aramburo · Accepted solution]

One to many is the better idea, here's another recommendation: if you have a User ID, ensure to have it on the Assignments/Claims table and then relate the Users ID single table containing all the hierarchy with it. That way you'll be able to filter the Claims table by the L3 Manager from your Users Catalog/Hierarchy. 

[BryMan_17 · Accepted solution]

Thank you for the reply.  The one-to-many approach seems like the better approach for some cases, as long as the many table does not become too large.

If I am following correctly, I believe that your alternative would require having filters for each level of manager.  Is that correct?

I did find a 3rd alternative, which I decided to use: using the HierarchySlicer custom visual along with a key on a ragged hierarchy chart.  Here are some links on it.  I was able to keep a proper star schema with this option:

Removing Blanks from Organizational Ragged Hierarchy in Power BI Matrix Visual - RADACAD

Parsing Organizational Hierarchy or Chart of Accounts in Power BI with Parent-child Functions in DAX...

[ray_aramburo]

Nice alternative! And to answer your question, yes, you'll need multiple filters but good thing is that you can create the hierarchy in the slicer itself 🙂

[BryMan_17]

Is there a way to remove the blanks in the default slicer when creating the hierachry?  Using the filter pane options to remove (Blank)s would remove parents. 

I believe the custom visual HierarchSlicer was created to solve this problem, but I'm curious if Power BI now has a away to do this in it's own Slicer now that I cannot find.

Thanks for your help on this!