Remove relationship and filter

[nyllshanson]

Can anyone help me understand how I can remove all filters that are applied to the just date dimension while applying a separate filter?

So let's say that I have 3 tables, orders, customers and date. The date table is the time dimension and I want to calculate a value by removing all filters applied to the date table that are propogated to the order table via a relationship, while also retaining any filters from the customer dimension, and adding a filter in the measure directly to the order table.

TestFormula%:= DIVIDE(CALCULATE(SUM(Orders[SellingQuantity]), ALL(Date), FILTER(Orders, Orders[DaysToEOM]<=-25))), CALCULATE(SUM(Orders[SellingQuantity]), ALL(Date)), 0)

So in this formula I want to ignore all Date filters, while allowing filters to customer specific attributes, and also adding a filter to the DaysToEOM in the orders table. Unfortunately I find that slicers on the Date dimension still effect this formula.

[ibarrau]

Hi there. Yes, you can help your measure ignore and allow different filters. For example:

Measure =
CALCULATE( 
    SUM ( Orders[Column1] ) ,
    ALLEXCEPT ( Orders, Customer[ColumnToFilter1], ... , Customer[ColumnsToFilterN] )
)

This will avoid any filter on the sum of column1 of orders excepting the columns added on ALLEXCEPT

Some doc: https://dax.guide/allexcept/

Regards,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[nyllshanson]

What if you want to say ignore "any filter in just this table", "keep filters in any other related table", and apply another filter using the filter function?

Date Table - Ignore all filters

Customer Table - Keep all filters

Order Table - Apply condition (ex. Orders[columnname]<={somevalue})

How can I write something like this?

[ibarrau]

I'm not sure you can keep it that simple. For that I would need a deep analysis of the data and understanding how you are showing the info. I know is tedious to add each column but you should just add the columns filtering in the page and not all of the customer table.

Regards,