Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

all() doesn't work in filter formula

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. 
 
 

pivot.PNG

 

4 REPLIES 4
Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

Here is the sales table. 

Order DateShip Datecategorysales amt

2019-01-012019-01-01coffee3
2019-01-012019-02-01tea2
2019-01-012019-02-01popcorn3
2019-01-012019-02-01chocolate4
2019-02-012019-02-01coffee4
2019-02-012019-02-01tea4
2019-02-012019-02-01popcorn4
2019-02-012019-02-01chocolate4
2019-03-012019-03-01coffee4
2019-03-012019-03-01tea4
2019-03-012019-03-01popcorn4
2019-03-012019-03-01chocolate4
2019-04-012019-04-01coffee4
2019-04-012019-04-01tea4
2019-04-012019-04-01popcorn4
2019-04-012019-04-01chocolate4

 

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

order date.PNG

Anonymous
Not applicable

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. 

@Anonymous Even if you author a measure as such, the result still unreasonable.Screenshot 2020-09-12 202148.png

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


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors