Table filters at a low level, but not at a high level (hierarchy from columns in multiple tables)

[benice25]

Problem

When I make a bridge table to handle duplicated rows from multiple values being selected and then try and use a column from the bridge table, it breaks one of my visuals (unless I select a specific project) how do I fix this?

Sample Data

This is a table that DOES NOT have duplicates, it's basically a listing of projects.

ISSUEID	PROJECT	PROJECT CATEGORY
10000	AZURE INSTALL	ADMIN WORK
10001	JIRA INSTALL	WORK MANAGEMENT
10002	CONFLUENCE INSTALL	WORK MANAGEMENT
10003	WEBLOAD INSTALL	TESTING
10004	SHAREPOINT INSTALL	WORK MANAGEMENT

Then we have a table that is basically a key to each project and the components associated to each one.

ISSUEID	COMPONENT
10000	MICROSOFT
10000	HIGHPRIORITY
10001	ATLASSIAN
10001	HIGHPRIORITY
10002	ATLASSIAN
10002	HIGHPRIORITY
10003	RADVIEW
10003	HIGHPRIORITY
10004	MICROSOFT
10004	HIGHPRIORITY

Then these two tables are linked together with a one to many relationship and a single filter direction where the projects would filter the components (if I do a filter in both directions it makes the result even worse 😞).

Expectations

I feel like if I were to put this straight into a visual table I should get something like this since the bridge is telling the projects which components are relevant to each one.

PROJECT CATEGORY	COMPONENT	PROJECT
ADMIN WORK	MICROSOFT	AZURE INSTALL
ADMIN WORK	HIGHPRIORITY	AZURE INSTALL
WORK MANAGEMENT	ATLASSIAN	JIRA INSTALL
WORK MANAGEMENT	HIGHPRIORITY	JIRA INSTALL
WORK MANAGEMENT	ATLASSIAN	CONFLUENCE INSTALL
WORK MANAGEMENT	HIGHPRIORITY	CONFLUENCE INSTALL
TESTING	RADVIEW	WEBLOAD INSTALL
TESTING	HIGHPRIORITY	WEBLOAD INSTALL
WORK MANAGEMENT	MICROSOFT	SHAREPOINT INSTALL
WORK MANAGEMENT	HIGHPRIORITY	SHAREPOINT INSTALL

Results

The results that I get back ARE NOT even close to this, why is this happening, and how do I fix it?

Since these are multi-value fields, I CANNOT simply merge one table to the other or else we get duplicates, so it's a hard and fast rule, no duplicates on the one side of the relationship, but if those values are not in the same table then I can't create a hierarchy...

PROJECT CATEGORY	COMPONENT	PROJECT
ADMIN WORK	MICROSOFT	AZURE INSTALL
ADMIN WORK	HIGHPRIORITY	AZURE INSTALL
ADMIN WORK	ATLASSIAN	AZURE INSTALL
ADMIN WORK	RADVIEW	AZURE INSTALL
WORK MANAGEMENT	MICROSOFT	JIRA INSTALL
WORK MANAGEMENT	HIGHPRIORITY	JIRA INSTALL
WORK MANAGEMENT	ATLASSIAN	JIRA INSTALL
WORK MANAGEMENT	RADVIEW	JIRA INSTALL
WORK MANAGEMENT	MICROSOFT	CONFLUENCE INSTALL
WORK MANAGEMENT	HIGHPRIORITY	CONFLUENCE INSTALL
WORK MANAGEMENT	ATLASSIAN	CONFLUENCE INSTALL
WORK MANAGEMENT	RADVIEW	CONFLUENCE INSTALL
TESTING	MICROSOFT	WEBLOAD INSTALL
TESTING	HIGHPRIORITY	WEBLOAD INSTALL
TESTING	ATLASSIAN	WEBLOAD INSTALL
TESTING	RADVIEW	WEBLOAD INSTALL
WORK MANAGEMENT	MICROSOFT	SHAREPOINT INSTALL
WORK MANAGEMENT	HIGHPRIORITY	SHAREPOINT INSTALL
WORK MANAGEMENT	ATLASSIAN	SHAREPOINT INSTALL
WORK MANAGEMENT	RADVIEW	SHAREPOINT INSTALL

What I Want to See

Here is an example of the report that worked before when I had a single value on the project table so we could make a hierarchy, and this is what I want... each project grouped by category, then by component (which are programs), and then list the project along with it's row values:

[v-rongtiep-msft]

Hi @benice25 ,

How about merging the 2 tables in the Power Query?

Or change the relationship between the 2 tables.

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[benice25]

If I merge the two tables, it creates duplicates on my fact table which is the one side of other relationships so PowerBI fails to refresh if I do that.

I believe I tried filtering in both directions, but it didn't really like that. I'll give it ago again to see if anything changes, thanks for the help.