Filtering both Parent and Child

[fullstrum]

Hello,

I have the following data structure:

Parent Table

ParentID

111

222

Child Table

ChildID	ParentID
5	111
6	222

Tag Table

Tag	ChildID	ParentID
Needs Review		111
Test	6	222

Basically, there is a parent table that has 0 to many children, and then a tag table, where the tag can either be on the Child (and thus I also have the childs parent ID), or on the Parent Directly.

My goal is to have a report that allows filtering by tag, where we will show the Parent (if the parent has the tag or one of it's children do), and any children of that parent that have the tag. 

So in the above example, if a user filtered for tag "Needs Review", they would only see ParentID 111, if they filtered for "Test", they would see ParentID 222 with child "6" below it.

If in SQL it would be something like...
Select *

From Parent p

Join Tag t on p.ParentID = t.ParentID 

LEFT Join Child c on c.ParentId = p.ParentID AND t.ChildId = c.ChildId

WHERE t.Tag = 'Test"

I have an active bi-directional relationship between Tags and Parent, and between Parent and Child.

In my matrix report, I can get the correct parents to show up when filtering by tag, but it is showing ALL the children below the parent, not just any children that might also have the given tag. Any ideas of what I might need to do to ONLY show the child if it also had the given tag?

[v-luwang-msft · Accepted solution]

Hi @fullstrum ,

Try to adjust the relationship  from the below  to the picture 3:

Don't forget to give thumbs up and accept this as a solution if it helped you!!!
Did I answer your question? Mark my post as a solution!

Best Regards

Lucien

[fullstrum]

It KINDA worked. In the matrix, it will show the correct parent/child... But if the parent had a child that did NOT have that tag, it'll show an empty row.

Also, having this relationship breaks a number of other reports. Which if I could get it to work right, would not be an issue (I'd create a different file to handle just this one case). All that to say, if there is a better solution out there, or a way to switch which relationship is being used for different filters, I'd love to hear it. 

[v-luwang-msft]

Hi  @fullstrum ,

For a matrix of hierarchical structures with empty rows removed, refer: 

https://www.daxpatterns.com/parent-child-hierarchies/

Hide Blanks Values in Matrix - Hierarchy

Best Regards

Lucien