Confused by ALL function

[gazpage]

Hi all,

I thought I understood the ALL() function as effectively being 'remove filters', in that if you use the ALL function on a table it will remove any filters directly on that table, but not filters that are imposed by other related tables.

In my example I have an Invoices table which lists all invoices with the amounts, date they were received, and date they were paid, if they have been. This invoices table is connected to the calendar via the Doc.Date column (active) and Clearing column (inactive). This structure allows be to look at total amounts of invoices received or paid (via USERELATIONSHIP).

In addition I want to look at the amount of invoices outstanding at a given point. To do this I want to cycle through my invoice table and check the received date and paid date against the last date in the current filter context, and include all invoices that have been received but not paid. The below is the formula I have come up with; you could ignore the IF and Future Invoices parts, as this is only intended to prevent the graph showing current invoices outstanding into the future.

The below actually works, but I don't understand why. I would expect the ALL ( Invoices) to remove filters on the Invoices table directly, but thought the invoice table should still be effected by the filter applied to the Calendar table.


TLDR: Don't understand why ALL ( Invoices ) in the below formula is also removing the filter coming from the linked calendar table.

Invoices Outstanding  = 
VAR EndDate =
    MAX ( 'Calendar'[Date] )
RETURN
    IF ( 
        [Future Invoices] =0,
        BLANK(),
        CALCULATE (
            [Net Invoices],
            FILTER (
                ALL ( Invoices ),
                Invoices[Doc. Date] <= EndDate
                    && (
                        Invoices[Clearing] > EndDate
                            || ISBLANK ( Invoices[Clearing] )
                    )
            )
        )
    )

 

[Matt Allington]

if you use the ALL function on a table it will remove any filters directly on that table, but not filters that are imposed by other related tables.

Well the answer is quite simple - this assumption is not correct. ALL removes any filter on the specified table (or columns(s)) AND any indirect filter propagated to the table. You can reapply filters coming from other tables by adding VALUES(table[column]) as an additional filter parameter inside CALCULATE.

 

[gazpage]

Well, I had to go back and check as you made me doubt myself. While doing that I seem to have answered by own question.

For reference it seems that All ( Table ) removes any filters imposed via cross filters, but All ( Table[Column] ) continues to respect them, ie only removes filters from that table. I guess I mostly use ALL on columns and so was used to that behaviour.

https://expirebox.com/download/466ec67f66580cc63a173f2cb20e7387.html

Saved a super simple model showing the effect here in case it is of interest.


I guess this leaves me with a follow up question. How do I remove the effect of a crossfilter if I don't want to remove the impact of all filters on my fact table and can't use ALL ( Table ) version. CROSSFILTER with None? Wrap my whole thing in a CALCULATE with ALL (Dimension table)?

 

[Matt Allington]

I'm not really sure what you mean with your follow up question when you say "cross filter". You can certainly next CALCULATE with ALL(Dim) as an outer clause if that solves your problem.

 

[gazpage]

Thanks, I think in hindsight this was obvious, but very helpful to bounce if off someone as I was stuck. In the end I just needed to iterate through the dates on my fact table with a filter, and then apply an ALL ( Dimension ) table wrapped around it.

Thanks again