compile discounts on master data based on matrix

[afuri]

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]

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]

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]

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

[afuri]

Hi Scee07,

Thanks for the help, I've created a  set  of  data  similar to my model and created  the workbook that you can find at the link below:

FILE PBX 

[scee07]

Hi, 

I looked at your file. Before proceeding I would like to check with you if we have the same understanding:

- SET is never empty in both tables (articles and discount)

- The other three attributes FAM,CAT,GRU can be empty or not in the discount table and in the article table. At least it looks the construction of dicounts codes is completely arbitrary from the sample you have given.

- If a SKU has a certain value for one of the attributes and it is not found in the discount table then still a discount with an empty value is applied (in the extreme A--- is saying apply the discount for set A and all others arbitrary and not found in a more specifc discount line)

- is this is true a SKU has 8 possible IDs e.g. A--- A-XXX--  A-YYY-ZZZ-, ..... That is, an attribute is specified or not, giving 1 x 2^3 possibilities. SET is always specified. 
- If we find matches in the discount table, the longest ID is taken, as this is the most specific discount. 

If what I am saying is not complete nonsense, the reason why is this is not easily working, is that you have to cover all possible combinations and find the most specific one. 

So. let me know if we have a common understanding. Then we can proceed to find a DAX solution. 

Best regards 
Christian

[afuri]

---

@scee07 ha scritto:

- SET is never empty in both tables (articles and discount)  CORRECT

- The other three attributes FAM,CAT,GRU can be empty or not in the discount table and in the article table. At least it looks the construction of dicounts codes is completely arbitrary from the sample you have given. CORRECT

- If a SKU has a certain value for one of the attributes and it is not found in the discount table then still a discount with an empty value is applied (in the extreme A--- is saying apply the discount for set A and all others arbitrary and not found in a more specifc discount line)  CORRECT, but if in the discount table there was no value in the SET field (wrongly) it would be better to insert the discount value "0" or empty in SKU (table2)

for example in this case SET Z is not mapped in the discount table, therefore SKU10 will have a discount equal to "0" or "blank"

tab1
SKU	SET	CAT	FAM	GRU
SKU10	Z	AAA	DDD	FFF
tab2
SET	CAT	FAM	GRU	VAL
A	SS	ASD	D	2
B	DD	SS		5
C		SSS		4
D	SSS		AA	5
E				7

- is this is true a SKU has 8 possible IDs e.g. A--- A-XXX--  A-YYY-ZZZ-, ..... That is, an attribute is specified or not, giving 1 x 2^3 possibilities. SET is always specified.  CORRECT

- Se troviamo corrispondenze nella tabella degli sconti, viene preso l'ID più lungo, in quanto questo è lo sconto più specifico. CORRECT

If we find matches in the discount table, the longest ID is taken, as this is the most specific discount. 

If what I am saying is not complete nonsense, the reason why is this is not easily working, is that you have to cover all possible combinations and find the most specific one. 

I confirm that you fully understand the logic of the tables and the tree of my dataset.

below is a representation of the table1 tree (skus master matrix)

Thanks for your support!

Andrea

---

[scee07 · Accepted solution]

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

[afuri]

Wow! you are a genius!
your approach is great, i tested the code on a complex model with 250k rows and it works great.
I'll mark your answer as a solution, I'll update you if I find any bugs.

Assumption is there is only one discount? If not firstnonblank can be replaced by sum and all discounts could be summed up (SET, CAT, ...)   Yes there is only one discount

---

Thank you for your precious help and I hope to hear from you soon!

Good weekend!

[yyzheng12]

Have you tried lookupvalue() dax? It allows more than one matching.

[afuri]

Hi yyzheng12

I've always used lookupvalue with single match. How can I make it consider multiple variables?

[yyzheng12]

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]

OK, that is better. I did not know either that you can look up multiple values. Good stuff.

[scee07]

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]

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]

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

[afuri]

You are right !
thanks for the precious help,

see you tomorrow