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

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

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

  • talespin's avatar
    talespin
    Icon for Solution Sage rankSolution Sage
    2 years ago

    hi DmitryAD7 

     

    You're welcome.