Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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)
Hi @Anonymous
It's a challenge to sovle it. I will take some time to think about it.
Best Regards,
Community Support Team _Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |