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