Dear hive mind!
Some background first...
I have a table with >200k rows - extract below;
| Cust ID | Cust Type | Cust Postcode | Parent ID | Invoice ID | Invoice Parent ID | Valid? |
| 377945 | Booking | WC7B 6NH | 377945 | 1161427 | 377945 | |
| 1089241 | Booking | CV75 ONR | 377945 | 1165160 | 377945 | |
| 1093354 | Booking | WC7B 6NH | 377945 | 1166118 | 377945 | |
| 1093661 | Booking | YO5 7PR | 377945 | 1166156 | 377945 | |
| 1122507 | Booking | WC7B 6NH | 377945 | 1185210 | 377945 | |
| 1134558 | Booking | YO5 7PR | 377945 | 1156897 | 1091197 | Error |
| 1135019 | Booking | KT55 5NB | 377945 | 1156897 | 1091197 | Error |
| 1135665 | Booking | NE4 7YH | 377945 | 1156897 | 1091197 | Error |
| 1135669 | Booking | MK9 5NS | 377945 | 1156897 | 1091197 | Error |
| 1139664 | Booking | YO5 7PR | 377945 | 1193803 | 377945 | |
| 1161427 | Invoice | WC7B 6NH | 377945 | 1161427 | 377945 | |
| 1165160 | Invoice | CV75 ONR | 377945 | 1165160 | 377945 | |
| 1166118 | Invoice | WC7B 6NH | 377945 | 1166118 | 377945 | |
| 1166156 | Invoice | YO5 7PR | 377945 | 1166156 | 377945 | |
| 1185210 | Invoice | WC7B 6NH | 377945 | 1185210 | 377945 | |
Each row pertains to a Customer record, which can either be an Invoice account or a Booking account.
Every Customer entity has a Parent ID, making them part of the same family.
Every Customer also has an Invoice ID, and each Invoice ID exists in the table as a separate row in the table.
The issue is that some of these Invoice accounts have a different Parent ID to the original Customer record, which makes them invalid, i.e. the Invoice account actually belongs to another family.
I've already written custom DAX ("Invoice Parent ID" column) to expose the Parent ID for each Invoice account, and another column which confirms if the Customer Parent ID differs from the associated Invoice Parent ID ("Valid?" column).
And so, to my problem.....!
I need to be able to identify if a Customer marked as "Error" has an alternative Invoice account within the same family with the same postcode,
In the example above the red text error row shares a postcode with the blue text non-error row, which is an Invoice account.
Whilst the green rows share a postcode with the red error row, and are not error rows themselves, they are invalid as they are Booking accounts, not Invoice accounts.
For every error row, if a single Invoice ID can be found, matching the above criteria, then I'd like to return this ID in a new column. If multiple valid alternatives exist, I'd like to return "Multiple" in the same column, and if no valid alternatives exist I'd like to return "None".
I'm convinced this must be doable....but sadly it's beyond my level of knowledge to figure out the best approach, so any suggestions would be greatfully received!!
Many thanks (and well done if you've managed to stick with me this far! haha)
