KEEPFILTERS need help

[DmitryAD7]

Hello community.

I need help with a measure. With two conditions:

- the customer ID "5335794105" must be excluding only from AA Department (regardless of any filters)

- if only 2 companies (fourth and fifth) are selected, the volume of one of them must be multiplied. Thanks @talespin with the measure (measure: 2. Sales with Condition).

I need help, because I can’t combine both conditions in one measure.

link to the file.

Thanks.

[Anonymous]

Hi, @DmitryAD7 

Unfortunately, due to the company's privacy policy, the link you shared doesn't open in my work environment, you can use GoogleDrive to share your pbix file (please note that when sharing, please don't set up a login to your Google account to access the link)

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[DmitryAD7]

Hello. Please try this link.

[Anonymous]

Hi, @DmitryAD7 

I can successfully open the link you shared, but there are still areas of confusion about your question:
1. what is the final result you want to achieve after filtering the data, a data table?
2. if you select only 2 companies (4th and 5th), you have to multiply the transaction volume of one of the companies. Here which two companies are multiplied or one of them is multiplied with other data, you need to describe your requirement in detail (use snapshot if you can)

Best Regards,
Yang
Community Support Team

[DmitryAD7]

For the first condition (with customer exclusion):
if one department is selected, totals are correct:

when two or more departments are selected, totals are incorrect:

[Anonymous]

Hi, @DmitryAD7 

Based on your description, I have created some measures to achieve the effect you are looking for. Following picture shows the effect of the display.

Measure:

1. Sell-out =

VAR _customer_excl =

    CALCULATE (

        SUM ( 'Dataset'[Value] ),

        FILTER ( 'Dataset', 'Dataset'[Customer ID] <> "5335794105" )

    )

VAR _default_value =

    CALCULATE ( SUM ( 'Dataset'[Value] ) )

VAR _dep =

    SELECTEDVALUE ( 'Dataset'[Departmet] )

VAR _ExceptDepartment = "AA Department"

VAR Result =

    IF (

        _ExceptDepartment IN ALLSELECTED ( 'Dataset'[Departmet] ),

        _customer_excl,

        _default_value

    )

RETURN

    Result

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[DmitryAD7]

Hello @Anonymous

Unfortunately, the results are incorrect. According to the condition, value should be excluded only for the Customer ID = "5335794105" in AA Department. Your DAX excluded Customer ID = "5335794105" from all deparments.

The expected total result (for all departments) is 57500 (59000-1500), for AA + BB is 11500 (13000-1500). Cause the Customer ID = "5335794105" value for AA Department is 1500.

[talespin]

hi @DmitryAD7 

Please try this measure.

Test =

VAR _count_check = DISTINCTCOUNT ( 'Dataset'[Company] )

VAR _exch_rate = 5

VAR _c1_value =

    CALCULATE (

        SUM ('Dataset'[Value]),        

        'Dataset'[Company] = "Fifth Company",

        NOT('Dataset'[Departmet] = "AA Department" && 'Dataset'[Customer ID] = "5335794105")

    )

VAR _c1_value_mp = _c1_value * _exch_rate

VAR _c2_value =

    CALCULATE (

        SUM ('Dataset'[Value]),

        'Dataset'[Company] = "Fourth Company",

        NOT('Dataset'[Departmet] = "AA Department" && 'Dataset'[Customer ID] = "5335794105")

    )

VAR _default_value = CALCULATE(SUM ('Dataset'[Value]), KEEPFILTERS(NOT('Dataset'[Departmet] = "AA Department" && 'Dataset'[Customer ID] = "5335794105")) )

VAR Result =

    IF (

        CONTAINSROW ( VALUES ( 'Dataset'[Company] ), "Fourth Company" ) && CONTAINSROW ( VALUES ( 'Dataset'[Company] ), "Fifth Company" ) && _count_check = 2,

        _c1_value_mp + _c2_value,

        _default_value

    )

RETURN Result

[DmitryAD7]

Hi @talespin ,

thanks a lot for your help, but I apologize for the incorrect info, in my work model Customer ID column is in another table and has a many-to-one relationship to the Dataset table. Link to correct file.

Please help to correct the code.

Test = 
VAR _count_check =
    DISTINCTCOUNT ( 'Dataset'[Company] )
VAR _exch_rate = 5
VAR _c1_value =
    CALCULATE (
        SUM ( 'Customers'[Value] ),
        'Dataset'[Company] = "Fifth Company",
        NOT ( 'Dataset'[Department] = "AA Department"
            && 'Customers'[Customer ID] = "5335794105" )
    )
VAR _c1_value_mp = _c1_value * _exch_rate
VAR _c2_value =
    CALCULATE (
        SUM ( 'Customers'[Value] ),
        'Dataset'[Company] = "Fourth Company",
        NOT ( 'Dataset'[Department] = "AA Department"
            && 'Customers'[Customer ID] = "5335794105" )
    )
VAR _default_value =
    CALCULATE (
        SUM ( 'Customers'[Value] ),
        KEEPFILTERS ( NOT ( 'Dataset'[Department] = "AA Department"
            && 'Customers'[Customer ID] = "5335794105" ) )
    )
VAR Result =
    IF (
        CONTAINSROW ( VALUES ( 'Dataset'[Company] ), "Fourth Company" )
            && CONTAINSROW ( VALUES ( 'Dataset'[Company] ), "Fifth Company" )
            && _count_check = 2,
        _c1_value_mp + _c2_value,
        _default_value
    )
RETURN
    Result

[talespin · Accepted solution]

hi @DmitryAD7 

Please use this measure.

Change the data type for Value column from test to number.

You have interaction disabled between Company Slicer and the table visual.

Test =

VAR _count_check =

    DISTINCTCOUNT ( 'Dataset'[Company] )

VAR _exch_rate = 5

VAR _c1_value =

    CALCULATE (

        SUM ( 'Customers'[Value] ),

        'Dataset'[Company] = "Fifth Company",

        KEEPFILTERS(NOT ( 'Customers'[Department] = "AA Department"

            && 'Customers'[Customer ID] = "5335794105" ) )

    )

VAR _c1_value_mp = _c1_value * _exch_rate

VAR _c2_value =

    CALCULATE (

        SUM ( 'Customers'[Value] ),

        'Dataset'[Company] = "Fourth Company",

        KEEPFILTERS(NOT ( 'Customers'[Department] = "AA Department"

            && 'Customers'[Customer ID] = "5335794105" ) )

    )

VAR _default_value =

    CALCULATE (

        SUM ( 'Customers'[Value] ),

        KEEPFILTERS ( NOT ( 'Customers'[Department] = "AA Department"

            && 'Customers'[Customer ID] = "5335794105" ) )

    )

VAR Result =

    IF (

        CONTAINSROW ( VALUES ( 'Dataset'[Company] ), "Fourth Company" )

            && CONTAINSROW ( VALUES ( 'Dataset'[Company] ), "Fifth Company" )

            && _count_check = 2,

        _c1_value_mp + _c2_value,

        _default_value

    )

RETURN

    Result

[DmitryAD7]

Hi @talespin . Thank you for your help. All works perfect!

[talespin]

hi @DmitryAD7 

You're welcome.

[DmitryAD7]

Hello, thank you for your question and time.

In general I need to calculate two conditions in one measure.

First condition:

Always (regardless of filters), only for one department “AA Department” customer ID "5335794105" was excluded from the total results.
How this measure is currently calculated:
Sell-out =
VAR_customer_excl =
CALCULATE (
[Base Measure],
KEEPFILTERS ( NOT ( 'Dataset'[Customer ID] = { "5335794105" } ) )
)
VAR_dep =
SELECTEDVALUE('Dataset'[Departmet])
VAR_default_value = [Base Measure]
VAR Result =
IF ( _dep = "AA Department", _customer_excl, _default_value )
RETURN
Result

But there is an error here, since when selecting several departments, total ignores the condition for excluding the customer ID "5335794105" from the “AA Department”.

Second condition:

If only 2 companies "Fourth Company" and "Fifth Company" were selected at the same time, the volume of "Fifth Company" was multiplied by ExchRate (VAR _exch_rate = 5). Under other conditions in the slicer, the multiplier is not used.
How this measure is currently calculated (this measure works fine.):

Sales with Condition =
VAR_count_check =
DISTINCTCOUNT('Dataset'[Company])
VAR_exch_rate = 5
VAR_c1_value =
CALCULATE (
[Base Measure],
REMOVEFILTERS('Dataset'[Company]),
'Dataset'[Company] = "Fifth Company"
)
VAR _c1_value_mp = _c1_value * _exch_rate
VAR_c2_value =
CALCULATE (
[Base Measure],
REMOVEFILTERS('Dataset'[Company]),
'Dataset'[Company] = "Fourth Company"
)
VAR_default_value =
CALCULATE (
[Base Measure],
REMOVEFILTERS('Dataset'[Company]),
VALUES ( 'Dataset'[Company] )
)
VAR Result =
IF (
CONTAINSROW ( VALUES ( 'Dataset'[Company] ), "Fourth Company" )
&& CONTAINSROW ( VALUES ( 'Dataset'[Company] ), "Fifth Company" )
&& _count_check = 2,
_c1_value_mp + _c2_value,
_default_value
)
RETURN
Result