Forum Discussion
Filtering both Parent and Child
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?
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 RegardsLucien
3 Replies
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 RegardsLucien
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.
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
