Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Sarath5c8
Frequent Visitor

required Delta of data

I need the delta of the data as the result 

when the table has 

IDParentChild
1P1C1
1P1C2
1P1C3
1P2C7
1P2C2
1P2C3
2P3C1
2P3C4
3P4C5
3P4C6
3P4C7
3P4C8
3P4C9
3P5C5
3P5C6
3P5C7
3P5C8
3P5C9

Output: 

IDParentChild
1P1C1
1P2C7

Note: ID 1 has 2 different parents and target is both should have same children under it (i.e no of children and value of children too) 
ID2 has only 1 parent so can be ignored.
ID3 had 2 parents and children are equal 
Hence output displays only mismatched once.
i did a recursive mapping but if an id has more than 10 parents and multiple children under it, tracking is becoming difficult. 
Can anyone help with an optimized solution possible.

 

1 ACCEPTED SOLUTION

Hi @Sarath5c8 ,

The Table data is shown below:

vzhouwenmsft_0-1715737817800.png

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

vzhouwenmsft_1-1715737959240.png

 

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.

View solution in original post

3 REPLIES 3
v-zhouwen-msft
Community Support
Community Support

Hi @Sarath5c8 ,

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

vzhouwenmsft_0-1715587693682.png

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

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.

Hi @Sarath5c8 ,

The Table data is shown below:

vzhouwenmsft_0-1715737817800.png

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

vzhouwenmsft_1-1715737959240.png

 

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.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.