Forum Discussion

DmitryAD7's avatar
DmitryAD7
Helper 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 (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.

  • 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

12 Replies

  • 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

      • 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