Forum Discussion
Assigning a transaction type based on a complex rules table
I have a transaction table containing a large number of fields.
I need to apply a transaction type to each transaction using a separate rules table. The rules are based on several fields and include logic such as IN, NOT IN, <, >
It is possible that there will be more than one matching rule per transaction.
I've tried to solve it in Power Query unsucessfully and I was wondering if a DAX measure may provide the result I am after.
A simplified example is below:
1. Transaction table
| Transaction ID | Attribute A | Attribute B |
| 1 | A | J |
| 2 | B | I |
| 3 | C | H |
| 4 | D | G |
| 5 | E | F |
| 6 | F | E |
| 7 | G | D |
| 8 | H | C |
| 9 | A | B |
| 10 | B | A |
Rules table
| Rule | Attribute A | Attribute B |
| 1 | IN(A,B,C) | IN(D,E) |
| 2 | NOT IN(C) | IN(A,B) |
| 3 | IN(D,E,F) | IN(E,F,G) |
| 4 | IN(G) | NOT IN (A,B,C,D) |
| 5 | NOT IN(A,B,C,D) | IN(H,I) |
| 6 | NOT IN(E,F,G) | NOT IN(H) |
| 7 | IN(A,B,E) | NOT IN (A,C,E,F) |
| 8 | IN(G,H) |
Desired result
| Transsaction ID | Applicable rules |
| 1 | 6,7 |
| 2 | 6,7 |
| 3 | |
| 4 | 3,6 |
| 5 | 3 |
| 6 | 3 |
| 7 | 8 |
| 8 | 8 |
| 9 | 2,7 |
| 10 | 2,6 |
If a rule is blank for a particular attritube (as rule 8, attribute B) then any value is a valid match for that rule.
Very much appreciate any help with this problem.
Hi Anonymous
Please see the attached file with the solution.
I know you asked for DAX but it was easier ( for Me ) to do it in Query Editor.Best Regards,
Mariusz
If this post helps, then please consider Accepting it as the solution.
2 Replies
- Mariusz
Community Champion
Hi Anonymous
Please see the attached file with the solution.
I know you asked for DAX but it was easier ( for Me ) to do it in Query Editor.Best Regards,
Mariusz
If this post helps, then please consider Accepting it as the solution.