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) :BRHCATDEPARTMENTSUBLEFT(SUB;3)SUB_NAMEACCTIN(ACCT_NAME)TARGETID_TARGETNumber of criteriaCriteria ListTARGET_NUMBER 0123456789 000123456789123456USD 3MR12SUB;ACCT;3 9876543210 12MR21SUB;1 000987654321456789JPY 6MR31ACCT;6EU 000888888888888888EUR 12MR42BRH;ACCT;1 DEP1 2MR51DEPARTMENT;2 AAA 12MR61CAT;1CABBB 3MR72BRH;CAT;3EUCCC 000888888888888888GBP 12MR83BRH;CAT;ACCT;1CNBBB PRESC12MR93BRH;CAT;IN(ACCT_NAME);1JPCCC 456 6MR103BRH;CAT;LEFT(SUB;3);6JPBBBDEP2 4MR113BRH;CAT;DEPARTMENT;4EUDDD MISCELLEANOUS 2MR123BRH;CAT;SUB_NAME;2Items table (null are replaced by a space for a better lisibility) :UIDBRHCATDEPARTMENTSUBSUB_NAMEACCTACCT_NAME1EUADEDEP990123456789Fees000123456789123456USDOTHER2 BBBDEP10123456789MISCELLEANOUS112233445566770889CADOTHER3 CCCDEP10123456789Fees000987654321456789JPYPRESCRIPTION 30y4CABBBDEP10123456789MISCELLEANOUS000888888888888888EUROTHER5EUCCCDEP10123456789MISCELLEANOUS000123456789123456USDPRESCRIPTION 5y6CNBBBDEP10123456789MISCELLEANOUS11223344556677889CADOTHER7JPDDDDEP10123456789MISCELLEANOUS112233445566770889CADOTHER8JPAAADEP10123456789MISCELLEANOUS000888888888888888GBPOTHER9EUBBBDEP10123456789MISCELLEANOUS456789456JPYPRESCRIPTION 5y10EUCCCDEP10123456789MISCELLEANOUS000123456789123456USDOTHER11 BBBDEP10123456789MISCELLEANOUS456789456JPYOTHERMy 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 vTableBRbut 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 ?

Hi Anonymous, 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.

Anonymous

Not applicable

1 year ago

Solved

Managing Business Rules with DAX

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

SUB;ACCT;

9876543210

12M

SUB;

000987654321456789JPY

ACCT;

000888888888888888EUR

12M

BRH;ACCT;

DEP1

DEPARTMENT;

AAA

12M

CAT;

BBB

BRH;CAT;

CCC

000888888888888888GBP

12M

BRH;CAT;ACCT;

BBB

PRESC

12M

BRH;CAT;IN(ACCT_NAME);

CCC

456

R10

BRH;CAT;LEFT(SUB;3);

BBB

DEP2

R11

BRH;CAT;DEPARTMENT;

DDD

MISCELLEANOUS

R12

BRH;CAT;SUB_NAME;

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 ?

Hi Anonymous,

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.

3 Replies

tayloramy

Super User

1 year ago

Hi Anonymous,

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.

Anonymous
Not applicable
1 year ago
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
  Super User
  1 year ago
  Happy to help!

Forum Discussion

Managing Business Rules with DAX

3 Replies

Related content

Power BI: How to consolidate allocation logic with different business rule across business units?

Realtime - Business Events - cannot create business event shcema

Configure and manage Activator rules directly in Eventstream (Preview)

How to stop rule firing

Business Requirement Document Template