topic Re: KEEPFILTERS need help in DAX Commands and Tips

KEEPFILTERS need help

DmitryAD7 — Sun, 03 Mar 2024 14:18:58 GMT

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.

Re: KEEPFILTERS need help

Anonymous — Mon, 04 Mar 2024 05:14:52 GMT

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

Re: KEEPFILTERS need help

DmitryAD7 — Mon, 04 Mar 2024 08:53:02 GMT

Hello. Please try this link.

Re: KEEPFILTERS need help

Anonymous — Wed, 06 Mar 2024 08:28:25 GMT

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

Re: KEEPFILTERS need help

DmitryAD7 — Wed, 06 Mar 2024 08:52:12 GMT

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

Re: KEEPFILTERS need help

DmitryAD7 — Wed, 06 Mar 2024 09:38:59 GMT

For the first condition (with customer exclusion):
if one department is selected, totals are correct:

when two or more departments are selected, totals are incorrect:

Re: KEEPFILTERS need help

Anonymous — Thu, 07 Mar 2024 08:07:04 GMT

Hi, DmitryAD7

Based on your description, I have created some measures to achieve the effect you are looking for. Following picture shows the effect of the display.

Measure:

1. Sell-out = VAR _customer_excl = CALCULATE ( SUM ( 'Dataset'[Value] ), FILTER ( 'Dataset', 'Dataset'[Customer ID] <> "5335794105" ) ) VAR _default_value = CALCULATE ( SUM ( 'Dataset'[Value] ) ) VAR _dep = SELECTEDVALUE ( 'Dataset'[Departmet] ) VAR _ExceptDepartment = "AA Department" VAR Result = IF ( _ExceptDepartment IN ALLSELECTED ( 'Dataset'[Departmet] ), _customer_excl, _default_value ) RETURN Result

Best Regards,
Yang
Community Support Team

How to get your questions answered quickly -- How to provide sample data in the Power BI Forum

Re: KEEPFILTERS need help

DmitryAD7 — Thu, 07 Mar 2024 09:41:38 GMT

Hello Anonymous

Unfortunately, the results are incorrect. According to the condition, value should be excluded only for the Customer ID = "5335794105" in AA Department. Your DAX excluded Customer ID = "5335794105" from all deparments.

The expected total result (for all departments) is 57500 (59000-1500), for AA + BB is 11500 (13000-1500). Cause the Customer ID = "5335794105" value for AA Department is 1500.

Re: KEEPFILTERS need help

talespin — Thu, 07 Mar 2024 10:46:28 GMT

hi DmitryAD7

Please try this measure.

Test =

VAR _count_check = DISTINCTCOUNT ( 'Dataset'[Company] )

VAR _exch_rate = 5

VAR _c1_value =

CALCULATE (

SUM ('Dataset'[Value]),

'Dataset'[Company] = "Fifth Company",

NOT('Dataset'[Departmet] = "AA Department" && 'Dataset'[Customer ID] = "5335794105")

)

VAR _c1_value_mp = _c1_value * _exch_rate

VAR _c2_value =

CALCULATE (

SUM ('Dataset'[Value]),

'Dataset'[Company] = "Fourth Company",

NOT('Dataset'[Departmet] = "AA Department" && 'Dataset'[Customer ID] = "5335794105")

)

VAR _default_value = CALCULATE(SUM ('Dataset'[Value]), KEEPFILTERS(NOT('Dataset'[Departmet] = "AA Department" && 'Dataset'[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

Re: KEEPFILTERS need help

DmitryAD7 — Thu, 07 Mar 2024 18:33:56 GMT

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

Re: KEEPFILTERS need help

talespin — Fri, 08 Mar 2024 03:25:53 GMT

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

Re: KEEPFILTERS need help

DmitryAD7 — Fri, 08 Mar 2024 09:49:56 GMT

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

Re: KEEPFILTERS need help

talespin — Fri, 08 Mar 2024 09:58:01 GMT

hi DmitryAD7

You're welcome.