Forum Discussion

Frequent Visitor

3 years ago

Solved

compile discounts on master data based on matrix

Hello Guys

I have two tables:

table_1 --> this table contains the manual mapping of a discount percentage. The mapping depends on the links entered in the table (maximum priority starting from the left) if you enter a value in the "SET" field, this has maximum priority.

table_1

the second table (table_2) contains all the items, based on table1 I should get the discount for each item:

table_2

the desired result would be the one below:

result

i tried to create an id column by concatenating all table1 values, but when i have only some elements filled in it doesn't work (example: only SET, FAM)

scee07

3 years ago

Hi,

here is my next approach. As discussed I constructed all relevant IDs. Logic as follows:
- if an attribute has no specific value "XXXX" is set in order to avoid the empty/blank problem
- then we have IDs of the form SET-CAT-FAM-GRU with A-XXXX-ABC-DE and the like.
- then an id 1111 means all attributes have a value and there are no blanks, e.g. 1000 means only the SET is non-blank and so on.
- in this terminology there are GRU discounts that have 1xx1 signature
- FAM discounts have a 1x1x signature and so forth
TBL2 gets the calculated columns as follows:

GRUDiscount =
VAR thisSET = TBL2[SET]
VAR thisFAM = TBL2[FAM]
VAR thisCAT = TBL2[CAT]
VAR thisGRU = TBL2[GRU]
VAR hasGRU =
    IF ( ISBLANK ( thisGRU ) = FALSE () && thisGRU <> "", TRUE (), FALSE () )
VAR hasSET =
    IF ( ISBLANK ( thisSET ) = FALSE () && thisSET <> "", TRUE (), FALSE () )
VAR hasCAT =
    IF ( ISBLANK ( thisCAT ) = FALSE () && thisCAT <> "", TRUE (), FALSE () )
VAR hasFAM =
    IF ( ISBLANK ( thisFAM ) = FALSE () && thisFAM <> "", TRUE (), FALSE () ) //GRU IDs 
/// 1111
VAR ID_1111 =
    IF (
        hasCAT && hasGRU
            && hasFAM,
        COMBINEVALUES ( "-", thisSET, thisCAT, thisFAM, thisGRU ),
        "None"
    ) //  1001
VAR ID_1001 =
    IF ( hasGRU, COMBINEVALUES ( "-", thisSET, "XXXX", "XXXX", thisGRU ), "None" ) // 1101 
VAR ID_1101 =
    IF (
        hasCAT && hasGRU,
        COMBINEVALUES ( "-", thisSET, thisCAT, "XXXX", thisGRU ),
        "None"
    ) // 1011
VAR ID_1011 =
    IF (
        hasGRU && hasFAM,
        COMBINEVALUES ( "-", thisSET, "XXXX", thisFAM, thisGRU ),
        "None"
    )
RETURN
    CALCULATE (
        FIRSTNONBLANK ( TBL1[VAL], 0 ),
        FILTER (
            TBL1,
            TBL1[ID] == ID_1111
                || TBL1[ID] == ID_1001
                || TBL1[ID] == ID_1101
                || TBL1[ID] == ID_1011
                && TBL1[VAL] <> 0
        )
    )

FAMDiscount =
VAR thisSET = TBL2[SET]
VAR thisFAM = TBL2[FAM]
VAR thisCAT = TBL2[CAT]
VAR thisGRU = TBL2[GRU]
VAR hasGRU =
    IF ( ISBLANK ( thisGRU ) = FALSE () && thisGRU <> "", TRUE (), FALSE () )
VAR hasSET =
    IF ( ISBLANK ( thisSET ) = FALSE () && thisSET <> "", TRUE (), FALSE () )
VAR hasCAT =
    IF ( ISBLANK ( thisCAT ) = FALSE () && thisCAT <> "", TRUE (), FALSE () )
VAR hasFAM =
    IF ( ISBLANK ( thisFAM ) = FALSE () && thisFAM <> "", TRUE (), FALSE () ) //FAM IDs 
/// 1110
VAR ID_1110 =
    IF (
        hasCAT && hasFAM,
        COMBINEVALUES ( "-", thisSET, thisCAT, thisFAM, "XXXX" ),
        "None"
    ) //  1010
VAR ID_1010 =
    IF ( hasFAM, COMBINEVALUES ( "-", thisSET, "XXXX", thisFAM, "XXXX" ), "None" )
RETURN
    CALCULATE (
        FIRSTNONBLANK ( TBL1[VAL], 0 ),
        FILTER ( TBL1, TBL1[ID] == ID_1110 || TBL1[ID] == ID_1010 && TBL1[VAL] <> 0 )
    )
CATDiscount = 
VAR thisSET = TBL2[SET]
VAR thisFAM = TBL2[FAM]
VAR thisCAT = TBL2[CAT]
VAR thisGRU = TBL2[GRU] 
VAR hasGRU = if(ISBLANK(thisGRU) = FALSE() && thisGRU <> "", TRUE(), FALSE())
VAR hasSET = if(ISBLANK(thisSET) = FALSE() && thisSET <> "", TRUE(), FALSE())
VAR hasCAT = if(ISBLANK(thisCAT) = FALSE() && thisCAT <> "", TRUE(), FALSE())
VAR hasFAM = if(ISBLANK(thisFAM) = FALSE() && thisFAM <> "", TRUE(), FALSE())

//CAT IDs 
/// 1100
VAR ID_1100 = if(hasCAT , COMBINEVALUES("-", thisSET, thisCAT, "XXXX", "XXXX"), "None")

return 
CALCULATE(FIRSTNONBLANK(TBL1[VAL],0), FILTER(TBL1, TBL1[ID] == ID_1100 && TBL1[VAL] <> 0))

SETDiscount =
VAR thisSET = TBL2[SET]
VAR thisFAM = TBL2[FAM]
VAR thisCAT = TBL2[CAT]
VAR thisGRU = TBL2[GRU]
VAR hasGRU =
    IF ( ISBLANK ( thisGRU ) = FALSE () && thisGRU <> "", TRUE (), FALSE () )
VAR hasSET =
    IF ( ISBLANK ( thisSET ) = FALSE () && thisSET <> "", TRUE (), FALSE () )
VAR hasCAT =
    IF ( ISBLANK ( thisCAT ) = FALSE () && thisCAT <> "", TRUE (), FALSE () )
VAR hasFAM =
    IF ( ISBLANK ( thisFAM ) = FALSE () && thisFAM <> "", TRUE (), FALSE () ) //SET IDs 
/// 1000
VAR ID_1000 =
    COMBINEVALUES ( "-", thisSET, "XXXX", "XXXX", "XXXX" )
RETURN
    CALCULATE (
        FIRSTNONBLANK ( TBL1[VAL], 0 ),
        FILTER ( TBL1, TBL1[ID] == ID_1000 && TBL1[VAL] <> 0 )
    )
Discount =
IF (
    TBL2[GRUDiscount] <> 0,
    TBL2[GRUDiscount],
    IF (
        TBL2[FAMDiscount] <> 0,
        TBL2[FAMDiscount],
        IF ( TBL2[CATDiscount] <> 0, TBL2[CATDiscount], TBL2[SETDiscount] )
    )
)

Basically, all cases are intended to be covered. In case there are multiple discounts, the most specific is applied.
Assumption is there is only one discount? If not firstnonblank can be replaced by sum and all discounts could be summed up (SET, CAT, ...)

The discount for SKU10 is now assigned. Here the updated file:
Discount.2.0.pbix
This is only tested in the sense that it reproduces the discounts for the 10 SKUs. You might test it further to find bugs. More importantly, it would be good to have a common understanding that this is one way to approach the problem. We can touch base on Monday, if necessary.
Best regards
Christian

16 Replies

scee07
Resolver I
3 years ago
Hi,
ID will not work as you look for strings contained in a string. E.g. set B has the requirement that CAT is FF. For an ID that only means that the ID contains B and FF.
I will just sketch a calculated column for Table2 to express how I would do it

Discount = VAR thisSET = Table2[SET] VAR thisFAM = Table2[FAM] VAR thisCAT = Table2[CAT] VAR thisGRU = Table2[GRU] // the combination of SKU and SET is unique: the SKU is unique and in only 1 set VAR necessaryFAM = CALCULATE ( FIRSTNONBLANK ( Table1[FAM], 0 ), FILTER ( Table1, Table1[SET] = thisSET ) ) VAR necessaryCAT = CALCULATE ( FIRSTNONBLANK ( Table1[CAT], 0 ), FILTER ( Table1, Table1[SET] = thisSET ) ) VAR necessaryGRU = CALCULATE ( FIRSTNONBLANK ( Table1[GRU], 0 ), FILTER ( Table1, Table1[SET] = thisSET ) ) VAR discount = CALCULATE ( FIRSTNONBLANK ( Table1[VAL], 0 ), FILTER ( Table1, Table1[SET] = thisSET ) ) VAR hasDiscount = ( thisFAM == necessaryFAM || ISBLANK ( necessaryFAM ) ) && ( thisCAT == necessaryFAM || ISBLANK ( necessaryCAT ) ) && ( thisGRU == necessaryGRU || ISBLANK ( necessaryGRU ) ) RETURN IF ( hasDiscount, discount )
Basically you get the necessary values from the discount table. The you say for every classifying column: either my current value matches or the required value is blank. Then in hasDiscount you check if all conditions are met. If yes, return the discount, if not return blank.
Not tested, as I have no time. But if you get the gist of it you be able to assign your discounts.
Best regards
Christian
- afuri
  Frequent Visitor
  3 years ago
  Hi Christian,
  
  thanks for the proposed solution, i wrote the code but i get wrong values
  I fixed this part:
  
  ...... thisCAT == necessaryCAT || ISBLANK(necessaryCAT) ) ......
  
  the column works but only reads the first combination, for example in the following case (SET F):
  
  SET CAT FAM GRU VAL
  F LGE FTH 5,00
  F LGE FTJ 5,00
  F LGE FTL 7,00
  F LGE FTM 2,00
  F LGE FVI 4,00
  
  The result is ok only for SET = F + CAT = LGE + FAM = FTH
  
  for others combinations result is BLANK
  ...
  - scee07
    Resolver I
    3 years ago
    Hi,
    as I said not tested. Will find time tomorrow to set this up with a toy model and come back to you.
    In principle this should work. But as always it has to be implemented to see the bugs.
    Best regards
    Christian
yyzheng12
Helper I
3 years ago
Have you tried lookupvalue() dax? It allows more than one matching.
- afuri
  Frequent Visitor
  3 years ago
  Hi yyzheng12
  I've always used lookupvalue with single match. How can I make it consider multiple variables?
  - yyzheng12
    Helper I
    3 years ago
    Its syntax is self-explanatory: just keeping the matching pairs in order.
    LOOKUPVALUE(
    <result_columnName>,
    <search_columnName>,
    <search_value>
    [, <search2_columnName>, <search2_value>]…
    [, <alternateResult>]
    )
scee07
Resolver I
3 years ago
Hi,
as per screenshot below I can reproduce your desired result:

Discount = VAR thisSET = Table2[SET] VAR thisFAM = Table2[FAM] VAR thisCAT = Table2[CAT] VAR thisGRU = Table2[GRU] // the combination of SKU and SET is unique: the SKU is unique and in only 1 set VAR necessaryFAM = CALCULATE ( FIRSTNONBLANK ( Table1[FAM], 0 ), FILTER ( Table1, Table1[SET] = thisSET ) ) VAR necessaryCAT = CALCULATE ( FIRSTNONBLANK ( Table1[CAT], 0 ), FILTER ( Table1, Table1[SET] = thisSET ) ) VAR necessaryGRU = CALCULATE ( FIRSTNONBLANK ( Table1[GRU], 0 ), FILTER ( Table1, Table1[SET] = thisSET ) ) VAR discount = CALCULATE ( FIRSTNONBLANK ( Table1[VAL], 0 ), FILTER ( Table1, Table1[SET] = thisSET ) ) VAR hasDiscount = ( (thisFAM == necessaryFAM) || (necessaryFAM = "") ) && ( (thisCAT == necessaryCAT) || (necessaryCAT = "") ) && ( (thisGRU == necessaryGRU) || (necessaryGRU = "") ) RETURN IF ( hasDiscount, discount )

A few remarks:
- when I created my tables the empty cells where not blank() in the DAX sense, the entries have been "". Then obviously isblank() does not work. So make sure that you now if your entries are blank or empty and adjust the formular accordingly. This is a classic in all database-type problems of this kind: is it null, empty or 0 or ""?
- lookupvalue: in this specific case here, the CAT, GRU, FAM value is found by filtering Table1 according to the SET the SKU is in. At least I have not grasped, how to do this with lookup
- The other thing is that you say if in table2 my value for CAT,GRU or FAM is empty then ignore the relevant entry for the SKU. In normal language this means, you don't have to lookup. So, not always a lookup case.
If somebody has a quicker solution, let me know.
Here is the link to the workbook.
Discount.pbix

Best regards
Christian
- afuri
  Frequent Visitor
  3 years ago
  thank you very much for your clarification,
  however testing the workbook I encountered the same problem as my model:
  
  adding a record (the last one in the table) - same "CAT" of record 1 but different "FAM"
  
  I don't get the desired result in output, I would have expected 44 discount on "SKU10"
  (SET = A, FAM=SS, CAT= DD)
  
  the hierarchy in order (from more specific to least specific) is:
  1) SKU (more specific)
  2) GRU
  3) FAM
  4) CAT
  5) SET ( least specific )
  
  setting only a value "SET" = A, VAL = 30 in TABLE_1 leaving all the remaining fields empty,
  I will act on all the "SKUs" which will have "SET" =A
  - scee07
    Resolver I
    3 years ago
    Hi,
    do you have an exhaustive set of examples that covers your business scenario?
    I will try to understand your business rules tomorrow when I find time and come back to you.
    
    Best regards
    Christian