business rules
1 Topic
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 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 ?Solved633Views0likes3Comments