Forum Discussion
Count duplicates with having constraint
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.
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" )
3 Replies
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" )You rock man.
I did not realize you could put a formula like that as a VAR. It also does not impact user experience.
