Forum Discussion

nyllshanson's avatar
nyllshanson
Frequent Visitor
6 years ago

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

  • ibarrau's avatar
    ibarrau
    Icon for Super User rankSuper User
    6 years ago

    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's avatar
      nyllshanson
      Frequent Visitor
      6 years ago

      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's avatar
        ibarrau
        Icon for Super User rankSuper User
        6 years ago

        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,