Need Help in DAX

[RahulShahane]

In the given Image I have multiple Parent IDs. For some of the Parent ID there are common Child IDs are there. If I select two or more Parent ID , I should get only common Child ID Values using DAX

e.g. Parent 2107 and 2108 are having 78,88,97 common Child IDs . So I need to display only Common Child IDs using Dax

sample Input / Output

 

[barritown]

Hi,

I'd propose to solve your case with such a measure:

Intersection of children = 
VAR Number_of_selected_parents = DISTINCTCOUNT( data[Parent] )
VAR Temp_table = FILTER ( SUMMARIZE (data, data[Child], "Qty of parents", COUNT ( data[Parent] ) ), [Qty of parents] = Number_of_selected_parents )
RETURN CONCATENATEX ( Temp_table, [Child], ", " )

Here's how it works:

[RahulShahane]

Hi Barritown , This solution really worked for me. Thanks. 

One more help, I need to show this coommon values in table column. Can u pls modify the measure for that.

[barritown · Accepted solution]

Hi @RahulShahane,

Since measures cannot output columns or tables, you need some trick.

For example, you can:

1. Create a table Children:

Children = DISTINCT ( data[Child] )

2. Set up a relationship with the main table.

3. Create a measure:

Flag = 
VAR Count_of_parents = COUNT ( data[Parent] )
VAR Total_count = CALCULATE ( DISTINCTCOUNT ( data[Parent] ), REMOVEFILTERS ( 'Children' ) )
RETURN Total_count - Count_of_parents

4. Add a table visual with the field 'Children'[Child] and the measure [Flag]

5. Set a filter in the visual "[Flag] = 0"

6. (optional) Add a rectangle to hide the [Flag] column.

Here's the output (w/o the rectangle):

If somebody proposes a cuter solution, I'll be happy to upgrade my knowledge. 🙂

[RahulShahane]

Thank you so much barritown for the solution. This has helped a lot. For any further Queries I will directly connect with u.

[JohnShepherdAPD]

Hi Rahul,

Sounds like you could filter the child IDs where they count >1 something like this:

IsCommon =

VAR _Tbl =

    ADDCOLUMNS (

        YourTable,

        "Count", CALCULATE ( COUNT ( YourTable[Child] ), YourTable[Parent] )

    )

RETURN

    SUMX ( _tbl, [Count] ) > 1

[RahulShahane]

Hi John Thanks for solution . But I need to display common record in table column , not just count. can u pls suggest solution.

[JohnShepherdAPD]

Hi Rahul, this expression returns a scalar value you can use to filter your visual:

just remove the ">1" part of the measure to turn it into a count then apply a visual filter where IsCommon >1 instead:

[RahulShahane]

Thank you so much John for the solution. In case of any further Query I will directly connect with u.