Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Need Help in DAX

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

 

 

Need Help
  • barritown's avatar
    barritown
    3 years ago

    Hi Anonymous,

    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. 🙂

8 Replies

  • JohnShepherdAPD's avatar
    JohnShepherdAPD
    Helper II
    3 years ago

    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
    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

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

      • JohnShepherdAPD's avatar
        JohnShepherdAPD
        Helper II
        3 years ago

        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:

         

         

  • barritown's avatar
    barritown
    Solution Sage
    3 years ago

    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:

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      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's avatar
        barritown
        Solution Sage
        3 years ago

        Hi Anonymous,

        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. 🙂