Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to Solution.
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.
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.
Thank you Mariusz for your very elegant PQ solution. I think this will work. Thank you very much for your help.
Simon
User | Count |
---|---|
42 | |
28 | |
24 | |
20 | |
16 |
User | Count |
---|---|
54 | |
35 | |
18 | |
18 | |
15 |