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

[Nakeele]

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

[tsumit07]

Also i need to use this inside a slicer.

What would be the best approach in this case?

[tsumit07]

Hi Nico , 

How could we use same measure for text values in Child column?

i am working on a similar issue where in my table Parent and child contains text values.

Really appreciate your help .

Thank you

S

[v-rzhou-msft · Accepted solution]

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.

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.

[Nakeele]

Works like a charm! Thanks!

[amitchandak]

@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))

[Nakeele]

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

[Nakeele]

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