Forum Discussion

Sarath5c8's avatar
Sarath5c8
Frequent Visitor
2 years ago
Solved

required Delta of data

I need the delta of the data as the result  when the table has  ID Parent Child 1 P1 C1 1 P1 C2 1 P1 C3 1 P2 C7 1 P2 C2 1 P2 C3 2 P3 C1 2 P3 C4 3 P4...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Sarath5c8 ,

    The Table data is shown below:

    Please follow these steps:
    1. Use the following DAX expression to create a measure

    MEASURE =
VAR _ChildCount =
    CALCULATE ( COUNTROWS ( 'Table' ), ALL ( 'Table'[Parent] ) )
VAR _ParentCount =
    CALCULATE (
        COUNTROWS ( SUMMARIZE ( 'Table', 'Table'[ID], 'Table'[Parent] ) ),
        ALLEXCEPT ( 'Table', 'Table'[ID] )
    )
RETURN
    IF ( _ParentCount = 1, BLANK (), IF ( _ChildCount <> 1, BLANK (), "" ) )

    2.Final output

     

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

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi Sarath5c8 ,

I don't quite understand your question. Is this your desired result?

Can you explain why you get 'C1' and 'C7'? Are you intending to show parent items with the same children under one ID?

  • Sarath5c8's avatar
    Sarath5c8
    Frequent Visitor
    2 years ago

    Yah that's the desired output because if you see under 1 we have two Parents P1 & P2 which has C2 & C3 common and C1 & C7 present only once hence those need to stand out and will be the result.

    if ID has 1 parent we can ignore it straight away.

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi Sarath5c8 ,

      The Table data is shown below:

      Please follow these steps:
      1. Use the following DAX expression to create a measure

      MEASURE =
VAR _ChildCount =
    CALCULATE ( COUNTROWS ( 'Table' ), ALL ( 'Table'[Parent] ) )
VAR _ParentCount =
    CALCULATE (
        COUNTROWS ( SUMMARIZE ( 'Table', 'Table'[ID], 'Table'[Parent] ) ),
        ALLEXCEPT ( 'Table', 'Table'[ID] )
    )
RETURN
    IF ( _ParentCount = 1, BLANK (), IF ( _ChildCount <> 1, BLANK (), "" ) )

      2.Final output

       

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