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

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

Reply
Anonymous
Not applicable

DAX ALL Function not working as expected

Hello,

 

I have a fairly basic problem that doesn't seem to rectify itself where I am trying to remove a filter context through the ALL() function, but the measure ignores this command and I do not get the expected result. From my screenshot below, I would expect the Rack Avg function to return the average of the five numbers for all rows with that date. There are no relationships to the source table and no columns sorted. Can someone explain why I may be having trouble?

 

Rack Measure = sum('Effective Rack Extracts'[Rack Price])

Rack Avg Measure = CALCULATE(AVERAGE('Effective Rack Extracts'[Rack Price]),all('Effective Rack Extracts'[Contract]))

 

Appreciate your help in advance; thank you.

 

Capture.JPG

6 REPLIES 6
Anonymous
Not applicable

try

Day Average Rack = 
    CALCULATE( 
        AVERAGE(Table1[Rack]), 
        ALLEXCEPT(Table1, Table1[date])
    )

Avg for all Date.png

Anonymous
Not applicable

Thanks for the idea, but the full table has six different attributes. I can specify them and then explicity specify any dimension tables I link the table to. It really doesn't seem like a sustainable solution, because if I add a new dimension table, I then have to go into the measure (and any measure like it) and add the new dimension table. I guess I'm surprised there isn't a simpler, more straightforward way to just remove a single filter instead of specifying all the filters I don't want removed. I know I can force an average through a calculated column because I can just calculate the average but that's really not best practice either. I'm interested in the proper syntex to unfilter the one column within the measure if it's possibe. Based upon this sqlbi.com article, they certainly do it, but I do not get the expected result at all. Is it because they are removing filters on the related Sales table instead of a filter in the Products table itself?

 

https://www.sqlbi.com/articles/managing-all-functions-in-dax-all-allselected-allnoblankrow-allexcept...

Hi montrealpie,

 

I found a solution reading the article you posted from sblbi.com. Apparently in my case I needed to include CALCULATETABLE in the filter expression of my measure in order to get the CALCULATE function to ingnore the date filter context. (I am trying to get the earliest date each lead was emailed regardless of the date filter context)

 

You can see the different behaviours in this screenshot:

 

 

 

Solution.jpg

 

 

My guesss  is it should work for you given the right parameters.

 

Best,

Paul.

 

PS. Despite the solution, I still don't understand why the ALL function does not remove the filter context by itself...
In fact, both measures using a simple ALL in my example return the same result as a simple MIN function In first column: MIN(Mails [date sent]) 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






For what it’s worth, I’m having the exact same issue, and asked for help in this thread:

https://community.powerbi.com/t5/Desktop/Minimum-Date-ALL-does-not-ignore-date-filter-context/m-p/68...

Baffled as to why ALL behaves this way...




Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Found this thread: Solved: ALL function not working as expected - Microsoft Power BI Community

 

I guess if your column is sorted by another column you need to have both of them in the all statement

This saved my day. Thank you!

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.