cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Nakeele
Regular Visitor

Select all children from Parent based on 1 child selected through a slicer

Hi all,

 

I've "Table A" where I have columns "Parents" and "Children" and Table B with "Children".

 

I have a report where a slicer based on a child from Table B drives all charts. But I'd like to display all children from the child's parent.

Unfortunately, it only displays the actual parent and child but not all the children.

 

Any thoughts?

 

Thank you!

 

N

1 ACCEPTED SOLUTION
RicoZhou
Community Support
Community Support

Hi @Nakeele 

According to your statement, I think your requirement is to get all children under the same parent based on the child you selected in slicer. Here I suggest you to create an unrelated child table to create the slicer.

Then create a measure, add this measure into filter field in your visual and set it to show items if the value equal to 1.

Try this code:

 

Filter = 
VAR _SelectChildren =
    SELECTEDVALUE ( 'Table B'[Children] )
VAR _Parent =
    CALCULATETABLE (
        VALUES ( 'Table A'[Parent] ),
        FILTER ( ALL ( 'Table A' ), 'Table A'[Children] = _SelectChildren )
    )
RETURN
    IF (
        ISFILTERED ( 'Table B'[Children] ),
        IF ( MAX ( 'Table A'[Parent] ) IN _Parent, 1, 0 ),
        1
    )

 

Result is as below.

1.png

Best Regards,
Rico Zhou

 

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

 

View solution in original post

5 REPLIES 5
RicoZhou
Community Support
Community Support

Hi @Nakeele 

According to your statement, I think your requirement is to get all children under the same parent based on the child you selected in slicer. Here I suggest you to create an unrelated child table to create the slicer.

Then create a measure, add this measure into filter field in your visual and set it to show items if the value equal to 1.

Try this code:

 

Filter = 
VAR _SelectChildren =
    SELECTEDVALUE ( 'Table B'[Children] )
VAR _Parent =
    CALCULATETABLE (
        VALUES ( 'Table A'[Parent] ),
        FILTER ( ALL ( 'Table A' ), 'Table A'[Children] = _SelectChildren )
    )
RETURN
    IF (
        ISFILTERED ( 'Table B'[Children] ),
        IF ( MAX ( 'Table A'[Parent] ) IN _Parent, 1, 0 ),
        1
    )

 

Result is as below.

1.png

Best Regards,
Rico Zhou

 

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

 

View solution in original post

Works like a charm! Thanks!

amitchandak
Super User
Super User

@Nakeele , Prefer to create an independent child table for slicer

 

a measure like

 

Meausre  =

var _parent = summarize(filter(TableA, TableA[Child] in selectedvalue(Child[Child]) ), TableA[parent])

var _child = summarize(filter(TableA, TableA[parent] in _parent ), TableA[Child])

return

counrows(filter(TableB, TableB[Child] in _child))

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

Actually, I get a message in the countrows: "The number of arguments is invalid. Function CONTAINSROW must have a value for each column in the table expression."

 

Countrows(filter('Table B','Table B'[child] in _child))

 

Trying to see what causes it but it's expecting something else somehow.

Sorry if the question is lame.

 

Thank you for your help!

 

N

Hi @amitchandak 

 

Thanks so much for the help. Juste a quick question, when you say "selectedvalue(Child[Child])", what are you referring to exactly?

 

Thanks again!

 

N

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.