How do I create a measure ignoring filters inside a table and keep selected filters outside?

[t_guet01]

Hey guys,

I am currently facing an issue that I use measures in a table, which should only consider certain filters but not all filters applied. I tried many combinations of all, allexcept, allselected, and removefilters, but I am not getting there.

The target table should look as follows:

Customer	Product	Sales	# Storages
A	P1	200	3
A	P2	400	3
B	P1	300	2
...	...	...	...

My target:

- I would like the measure "Sales" to respect the filtering within the table, so "Customer" and "Product" dimension. I would also like the measure to respect the filters "Year" and "Product" from outside, but no others filters (e.g. "Sales Region")

- I would like the measure "Storages" to respect only filtering by "Customer" within the table (but not "Product") and only "Year" and "Products" from outside the table (but not "Sales Region" etc.).

Current measures:

- Sales = SUM ( 'Orders'[Net] )

- Storages = Countdistinct ( 'Orders'[StorageID] )

Filter pane: 

- Year = 2022

- Products: P1 and P2

- Sales region: EMEA

- ... (more filters)

My input data look as follows:

Orders:

CustomerID	ProductID	Date	Net	StorageID
C987	147852	01.01.2022	10	S123
C654	369258	02.01.2022	15	S456

Customer data:

CustomerID	CustomerName	Sales Region	...
C987	A	EMEA	...
C654	B	LATAM	...

Product data:

ProductID	ProductName	...
147852	P1	...
369258	P2	...

How do I need to edit the measures to have the aimed effect?

Thanks and best

T

[Anonymous · Accepted solution]

Hi @t_guet01,

I'd like to suggest you take a look at the following blog about all functions that can be used to ignore the specific filter effects.

Managing “all” functions in DAX: ALL, ALLSELECTED, ALLNOBLANKROW, ALLEXCEPT - SQLBI

In addition, you can also consider breaking or turning off the 'active' relationships and use TREATAS or USERELATIONSHIP functions to calculate with specific filter effects in your expression.

Power BI USERELATIONSHIP vs TREATAS - Enterprise DNA

Regards,

Xiaoxin Sheng