Count duplicates with having constraint

[bvanevr]

I am working with data quality visualization and trying to count for duplicates with a filter and not seeing how to accomplish.  Below is some made up data that should help explain issue:

Brand	Child	Parent	surrogate
ST	1	A	123
ST	2	A	123
ST	3	B	456
ST	4	B	789
LR	5	C	567
LR	6	C	890

First I need a filter for just Brand = ST, then for those that pass the filter I need validate a rule of each Parent has one Surrogate.

This is what I am looking for as Brand LR is not relevant thus Duplicate = NA, Parent A passes the duplicate rule since there is only one surrogate and Parent B Fails because there are multiple surrogates.

Brand	Child	Parent	surrogate	Duplicate
ST	1	A	123	Pass
ST	2	A	123	Pass
ST	3	B	456	Failure
ST	4	B	789	Failure
LR	5	C	567	N/A
LR	6	C	890	N/A

I have tried both DAX and M (power query) and am not able to get desired resutls.

For M I referenced https://www.excelguru.ca/blog/2015/12/09/identify-duplicates-using-power-query/ .  This does not work because Parent A has multiple rows and it count this as a duplicate and for me it is not since it only has one Surrogate.

For DAX I referenced https://community.powerbi.com/t5/Desktop/Identify-duplicates-and-show-up-records/td-p/210061.  This does not work because my data set is large and it returns with out of memory error.

[Zubair_Muhammad · Accepted solution]

@bvanevr

Try This Calculated Column

Duplicates =
VAR mycount =
    CALCULATE (
        DISTINCTCOUNT ( TableName[surrogate] ),
        ALLEXCEPT ( TableName, TableName[Parent] )
    )
RETURN
    IF ( TableName[Brand] = "ST", IF ( mycount = 1, "Pass", "Failure" ), "N/A" )

[Zubair_Muhammad]

@bvanevr

[bvanevr]

You rock man.

I did not realize you could put a formula like that as a VAR.  It also does not impact user experience.