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 ...

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

afuri

Frequent Visitor

3 years ago

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

Resolver I

3 years ago

Hi,

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] )
    )
)

afuri
Frequent Visitor
3 years ago
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!

Forum Discussion

compile discounts on master data based on matrix

Related content

Discount for certifications

PL 300 Exam Voucher or Discounts

Master Data Service in Fabric ?

Calculate discount

Master Models

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