Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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 IDAttribute AAttribute B
1AJ
2BI
3CH
4DG
5EF
6FE
7GD
8HC
9AB
10BA

 

Rules table

RuleAttribute AAttribute B
1IN(A,B,C)IN(D,E)
2NOT IN(C)IN(A,B)
3IN(D,E,F)IN(E,F,G)
4IN(G)NOT IN (A,B,C,D)
5NOT IN(A,B,C,D)IN(H,I)
6NOT IN(E,F,G)NOT IN(H)
7IN(A,B,E)NOT IN (A,C,E,F)
8IN(G,H) 

 

Desired result

Transsaction IDApplicable rules
16,7
26,7
3 
43,6
53
63
78
88
92,7
102,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.

 

 

1 ACCEPTED SOLUTION
Mariusz
Community Champion
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.

 

 

View solution in original post

2 REPLIES 2
Mariusz
Community Champion
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.

 

 

Anonymous
Not applicable

Thank you Mariusz for your very elegant PQ solution. I think this will work. Thank you very much for your help.

Simon

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors