topic Re: all() doesn't work in filter formula in DAX Commands and Tips

all() doesn't work in filter formula

Anonymous — Fri, 11 Sep 2020 16:24:12 GMT

Hi,

I created a small sales volumen table (fact table), this table is linked to order date table (dimension table). I then created a measure as below, which works normally.

sales vol Jan = calculate(sum(sales[sales amt]),filter('order date', 'order date'[order month]="2019-01"))

however, something strange happened when it add all() to the filter formula.

sales vol January3 = calculate(sum(sales[sales amt]),filter(all('order date'), 'order date'[order month]="2019-01"))

It looks like the all() has caused a problem here, not sure about the reason.

Re: all() doesn't work in filter formula

Greg_Deckler — Fri, 11 Sep 2020 16:30:19 GMT

Anonymous Would need to understand your source data and expected output but yes, ALL can dramatically alter a calculation because you are essentially ignoring all filter context that have been placed on that calculation like column you are in, row you are in, any visual filters, etc.

Re: all() doesn't work in filter formula

Anonymous — Fri, 11 Sep 2020 16:44:23 GMT

Here is the sales table.

Order DateShip Datecategorysales amt

2019-01-01	2019-01-01	coffee	3
2019-01-01	2019-02-01	tea	2
2019-01-01	2019-02-01	popcorn	3
2019-01-01	2019-02-01	chocolate	4
2019-02-01	2019-02-01	coffee	4
2019-02-01	2019-02-01	tea	4
2019-02-01	2019-02-01	popcorn	4
2019-02-01	2019-02-01	chocolate	4
2019-03-01	2019-03-01	coffee	4
2019-03-01	2019-03-01	tea	4
2019-03-01	2019-03-01	popcorn	4
2019-03-01	2019-03-01	chocolate	4
2019-04-01	2019-04-01	coffee	4
2019-04-01	2019-04-01	tea	4
2019-04-01	2019-04-01	popcorn	4
2019-04-01	2019-04-01	chocolate	4

It's linked to 'order date' table via 'order date' column. Below is a screen shot of 'order date' table.

Re: all() doesn't work in filter formula

Anonymous — Fri, 11 Sep 2020 17:03:10 GMT

I found the reason. I have created a new column called sales vol Juanary3. I meant to create a new measure, which is clearly not the case. I should have checked the fields panel more carefully.

Re: all() doesn't work in filter formula

CNENFRNL — Sat, 12 Sep 2020 19:25:40 GMT

Anonymous Even if you author a measure as such, the result still unreasonable.

As the FILTER(ALL(order date), ... ) returns a table with a column 'order date'[order month]="2019-01", it overrides all other filter context from the matrix, say [order month]="2019-02", "2019-03"..."2019-12", thus every row of matrix will be filled with sum(sales[sales amt]) of "2019-01".