Forum Discussion

DmitryAD7's avatar
DmitryAD7
Icon for Helper I rankHelper I
2 years ago
Solved

KEEPFILTERS need help

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 (fou...
  • talespin's avatar
    talespin
    2 years ago

    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
Anonymous's avatar
Anonymous
Not applicable
2 years ago

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's avatar
    DmitryAD7
    Icon for Helper I rankHelper I
    2 years ago

    Hello. Please try this link.

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      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's avatar
        DmitryAD7
        Icon for Helper I rankHelper I
        2 years ago

        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