Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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 | C5 |
3 | P4 | C6 |
3 | P4 | C7 |
3 | P4 | C8 |
3 | P4 | C9 |
3 | P5 | C5 |
3 | P5 | C6 |
3 | P5 | C7 |
3 | P5 | C8 |
3 | P5 | C9 |
Output:
ID | Parent | Child |
1 | P1 | C1 |
1 | P2 | C7 |
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.
Solved! Go to Solution.
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.
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?
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:
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.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
87 | |
84 | |
73 | |
49 |
User | Count |
---|---|
143 | |
131 | |
110 | |
64 | |
55 |