Managing Business Rules with DAX

[marko24]

Hello everyboody,

I have some issue regarding Business Rules. Indeed, I'm trying to build a Business Rules engine with DAX.
My first solution is too hard too maintain... Other try I've made are failed...

I think it's a complex business rules engines, because it's based on SOME criteria, not all (so LOOKUPVALUE is excluded).

Indeed, for example, the ID_TARGT = R1 will be applied for an item IF, for this item, SUB = 0123456789 AND ACCT=000123456789123456USD WHATEVER the value of the other columns for the item...

Business rules table (null are replaced by a space for a better lisibility) :

BRH

CAT

DEPARTMENT

SUB

LEFT(SUB;3)

SUB_NAME

ACCT

IN(ACCT_NAME)

TARGET

ID_TARGET

Number of criteria

Criteria List

TARGET_NUMBER

0123456789

000123456789123456USD

3M

R1

2

SUB;ACCT;

3

9876543210

12M

R2

1

SUB;

1

000987654321456789JPY

6M

R3

1

ACCT;

6

EU

000888888888888888EUR

12M

R4

2

BRH;ACCT;

1

DEP1

2M

R5

1

DEPARTMENT;

2

AAA

12M

R6

1

CAT;

1

CA

BBB

3M

R7

2

BRH;CAT;

3

EU

CCC

000888888888888888GBP

12M

R8

3

BRH;CAT;ACCT;

1

CN

BBB

PRESC

12M

R9

3

BRH;CAT;IN(ACCT_NAME);

1

JP

CCC

456

6M

R10

3

BRH;CAT;LEFT(SUB;3);

6

JP

BBB

DEP2

4M

R11

3

BRH;CAT;DEPARTMENT;

4

EU

DDD

MISCELLEANOUS

2M

R12

3

BRH;CAT;SUB_NAME;

2

Items table (null are replaced by a space for a better lisibility) :

UID	BRH	CAT	DEPARTMENT	SUB	SUB_NAME	ACCT	ACCT_NAME
1	EU	ADE	DEP99	0123456789	Fees	000123456789123456USD	OTHER
2		BBB	DEP1	0123456789	MISCELLEANOUS	112233445566770889CAD	OTHER
3		CCC	DEP1	0123456789	Fees	000987654321456789JPY	PRESCRIPTION 30y
4	CA	BBB	DEP1	0123456789	MISCELLEANOUS	000888888888888888EUR	OTHER
5	EU	CCC	DEP1	0123456789	MISCELLEANOUS	000123456789123456USD	PRESCRIPTION 5y
6	CN	BBB	DEP1	0123456789	MISCELLEANOUS	11223344556677889CAD	OTHER
7	JP	DDD	DEP1	0123456789	MISCELLEANOUS	112233445566770889CAD	OTHER
8	JP	AAA	DEP1	0123456789	MISCELLEANOUS	000888888888888888GBP	OTHER
9	EU	BBB	DEP1	0123456789	MISCELLEANOUS	456789456JPY	PRESCRIPTION 5y
10	EU	CCC	DEP1	0123456789	MISCELLEANOUS	000123456789123456USD	OTHER
11		BBB	DEP1	0123456789	MISCELLEANOUS	456789456JPY	OTHER

My first solution is too complex to maintain : it's a COALESCE with several LOOKUPVALUE for each type of criteria :

TARGET_EXPECTED_RESULT = 
    COALESCE ( 
    -- rules with only one criteria
        LOOKUPVALUE(
            Business_Rules[TARGET],
            Business_Rules[Criteria List], "CAT;",
            Business_Rules[CAT], [CAT]),
        LOOKUPVALUE(
            Business_Rules[TARGET],
            Business_Rules[Criteria List], "SUB;",
            Business_Rules[SUB], [SUB]),
...

    -- rules with two criteria        
        LOOKUPVALUE(
            Business_Rules[TARGET],
            Business_Rules[Criteria List], "BRH;CAT;",
            Business_Rules[BRH], [BRH],
            Business_Rules[CAT], [CAT]),

...

    -- rules with three criteria   
        LOOKUPVALUE(
            Business_Rules[TARGET],
            Business_Rules[Criteria List], "BRH;CAT;ACCT;",
            Business_Rules[BRH], [BRH],
            Business_Rules[CAT], [CAT],
            Business_Rules[ACCT], [ACCT]),    
        
...

    -- specific rules with three criteria with one with *specific text* into acc_name  
        IF(CONTAINSSTRING([ACCT_NAME],"PRESC") = TRUE(),
            LOOKUPVALUE(
                Business_Rules[TARGET],
                Business_Rules[Criteria List], "BRH;CAT;IN(ACCT_NAME);",
                Business_Rules[BRH], [BRH],
                Business_Rules[CAT], [CAT]),
            "unknown"),
        "unknown"    
    )

So, I've tried a new solution : 

find_BR_v3 = 
VAR vTableBR = 
       CALCULATE(
        MAX(Business_Rules[TARGET]),
            FILTER(Business_Rules,
            Business_Rules[BRH] IN {[BRH],BLANK()} && 
            Business_Rules[CAT] IN {[CAT],BLANK()} && 
            Business_Rules[DEPARTMENT] IN {[DEPARTMENT],BLANK()} && 
            Business_Rules[SUB] IN {[SUB],BLANK()} && 
            Business_Rules[LEFT(SUB;3)] IN {LEFT([SUB],3),BLANK()} && 
            Business_Rules[SUB_NAME] IN {[SUB_NAME],BLANK()} && 
            Business_Rules[ACCT] IN {[ACCT],BLANK()} && 
            Business_Rules[IN(ACCT_NAME)] IN {[IN(ACCT_NAME)],BLANK()}
            )
       )

RETURN 
vTableBR

but in this case, ALL rows have the same value (and not the expected value of course) ...
I presume there is an issue regarding row context/level... But I'm lost...


Any idea ?
 

[tayloramy · Accepted solution]

Hi @marko24,

You can absolutely model this as a “wildcard-friendly” rules match in DAX without the giant COALESCE/LOOKUPVALUE chain. The trick is to treat blanks in the rules table as wildcards, score each rule by specificity, then pick the most specific match (or use your TARGET_NUMBER as a tiebreaker).

Below is a compact pattern that’s easy to maintain and runs fast.

Adds the best matching TARGET from Business_Rules to every row in Items.

// In Items table
TARGET =
VAR itemBRH       = Items[BRH]
VAR itemCAT       = Items[CAT]
VAR itemDEPT      = Items[DEPARTMENT]
VAR itemSUB       = Items[SUB]
VAR itemSUBNAME   = Items[SUB_NAME]
VAR itemACCT      = Items[ACCT]
VAR itemACCTNAME  = Items[ACCT_NAME]

VAR Matches =
    FILTER (
        ADDCOLUMNS (
            ALL ( Business_Rules ),                      // ignore current filters on rules
            "__isMatch",
                /* Treat blanks in rules as wildcards; otherwise require equality.
                   Handle special columns LEFT(SUB;3) and IN(ACCT_NAME). */
                ( ISBLANK ( Business_Rules[BRH] )              || Business_Rules[BRH] = itemBRH ) &&
                ( ISBLANK ( Business_Rules[CAT] )              || Business_Rules[CAT] = itemCAT ) &&
                ( ISBLANK ( Business_Rules[DEPARTMENT] )       || Business_Rules[DEPARTMENT] = itemDEPT ) &&
                ( ISBLANK ( Business_Rules[SUB] )              || Business_Rules[SUB] = itemSUB ) &&
                ( ISBLANK ( Business_Rules[SUB_NAME] )         || Business_Rules[SUB_NAME] = itemSUBNAME ) &&
                ( ISBLANK ( Business_Rules[ACCT] )             || Business_Rules[ACCT] = itemACCT ) &&
                ( ISBLANK ( Business_Rules[LEFT(SUB;3)] )      || Business_Rules[LEFT(SUB;3)] = LEFT ( itemSUB, 3 ) ) &&
                ( ISBLANK ( Business_Rules[IN(ACCT_NAME)] )    || CONTAINSSTRING ( itemACCTNAME, Business_Rules[IN(ACCT_NAME)] ) )
        ),
        [__isMatch]
    )

VAR BestRule =
    TOPN (                                         // pick the “best” rule
        1,
        Matches,
        Business_Rules[Number of criteria], DESC,  // prefer more specific
        Business_Rules[TARGET_NUMBER], ASC         // tiebreaker (your priority)
    )
RETURN
    COALESCE ( MAXX ( BestRule, Business_Rules[TARGET] ), "unknown" )

Why this works:

• ALL() clears context on the rules table so each row is tested cleanly (ALL).
• ADDCOLUMNS() gives us a per-rule boolean “match” (ADDCOLUMNS).
• CONTAINSSTRING() handles your “IN(ACCT_NAME)” contains-logic (CONTAINSSTRING).
• TOPN() returns the most specific match with a deterministic order (TOPN).
• FILTER() applies the match predicate (FILTER).

If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution.

[marko24]

Hi tayloramy,

many thanks : it's works but more important : your answer is very clear, detailled and will helped me in the future

thanks again !!

have a nice day

[tayloramy]

Happy to help!