cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Remove relationship and filter

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.

3 REPLIES 3
Super User

``````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.

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

Happy to help!

Frequent Visitor

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?

Super User

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,

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

Happy to help!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors