Forum Discussion
DmitryAD7
Helper I
Helper IKEEPFILTERS 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...
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 = 5VAR _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_rateVAR _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)RETURNResult
talespin
Solution Sage
Solution Sagehi 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
