topic Remove relationship and filter in DAX Commands and Tips

Remove relationship and filter

nyllshanson — Tue, 04 Feb 2020 16:59:33 GMT

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.

Re: Remove relationship and filter

ibarrau — Tue, 04 Feb 2020 18:12:27 GMT

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.

Re: Remove relationship and filter

nyllshanson — Tue, 04 Feb 2020 18:40:47 GMT

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?

Re: Remove relationship and filter

ibarrau — Tue, 04 Feb 2020 19:22:55 GMT

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,