give the max(tradediscountprice) for all in the custgroup, but not in the selected channel descripti

[eunji888888]

It give the max(tradediscountprice) for all in the custgroup, but not in the selected channel description. How to solve?

StdPrice =

VAR EANValue = SELECTEDVALUE('CustGroup4 Material'[#EAN])

VAR ChannelValue = SELECTEDVALUE('SO'[Channel Description])

var CustGroup= SELECTEDVALUE('CustGroup4 Material'[#CustGroup4_Channel Description])

VAR TradeDiscountPrice =

    CALCULATE(

        MAXX(FILTER(ALLEXCEPT('CustGroup4 Material', 'CustGroup4 Material'[#EAN], 'A814 CustGroup4 Material'[#CustGroup4_Channel Description]), RIGHT('CustGroup4 Material'[#ConditionType Description],1)= "%"), 'CustGroup4 Material'[StdPrice]),

        'CustGroup4 Material'[Promotion yes/no] = "Promotion Disc"

    )

RETURN TradeDiscountPrice

[Anonymous · Accepted solution]

Hi @eunji888888 

For your question, here is the method I provided:

Here's some dummy data

"SO"

"CustGroup4 Material"

I have made changes to your code as follows:

Measure StdPrice = 
VAR EANValue = SELECTEDVALUE('CustGroup4 Material'[#EAN])
VAR ChannelValue = SELECTEDVALUE('SO'[Channel Description])
VAR CustGroup = SELECTEDVALUE('CustGroup4 Material'[#CustGroup4_Channel Description])
VAR TradeDiscountPrice =
    CALCULATE(
        MAXX(
            FILTER(
                ALLEXCEPT('CustGroup4 Material', 'CustGroup4 Material'[#EAN]),
                RIGHT('CustGroup4 Material'[#ConditionType Description], 1) = "%" &&
                'CustGroup4 Material'[#CustGroup4_Channel Description] = ChannelValue
            ),
            'CustGroup4 Material'[StdPrice]
        ),
        'CustGroup4 Material'[Promotion yes/no] = "Promotion Disc"
    )

RETURN TradeDiscountPrice

Here is the result.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[SolomonovAnton]

To ensure that the MAX(tradediscountprice) function considers only the CustGroup and filters by the selected Channel Description, you need to incorporate the Channel Description filter in your calculation. Here’s how to modify your DAX code: Include Channel Description as a filtering condition in the CALCULATE function. Use the ALLEXCEPT function with Channel Description as an additional parameter to keep it in the current context.

StdPrice =
VAR EANValue = SELECTEDVALUE('CustGroup4 Material'[#EAN])
VAR ChannelValue = SELECTEDVALUE('SO'[Channel Description])
VAR CustGroup = SELECTEDVALUE('CustGroup4 Material'[#CustGroup4_Channel Description])

VAR TradeDiscountPrice =
CALCULATE(
MAXX(
FILTER(
ALLEXCEPT(
'CustGroup4 Material',
'CustGroup4 Material'[#EAN],
'CustGroup4 Material'[#CustGroup4_Channel Description],
'SO'[Channel Description] // Keep Channel Description in context
),
RIGHT('CustGroup4 Material'[#ConditionType Description], 1) = "%"
),
'CustGroup4 Material'[StdPrice]
),
'CustGroup4 Material'[Promotion yes/no] = "Promotion Disc",
'SO'[Channel Description] = ChannelValue // Filter for selected Channel Description
)

RETURN TradeDiscountPrice