Need help with ALL function (DAX)

[parry2k]

Hi all,

Looking for advice on following, i have a calendar table and there is a field called "Days From Today", basically it is a calculated field, making current day as 0 and yesteray as -1 and so on....

I also have another field in calendar table called "CurrentWeekDay" with value 0 or 1 for example, on friday, all dates with week day friday will be 0 and rest will be 1 and on Saturday, all dates with saturday will be 0 and rest 1. Both fields are coming from SQL database. 

I have another data table called "Calls" and related with Calendar Table on Date. 

THere is 3rd table, not related to anything, it is called "Average for how many days", basically it has two values "Days" and "Label", using this as a slicer to select for how many days a user want to calculated the average for, like last 7 Days, 15 days, 30 days etc...

Also I'm creating a measure to store default value for average days in case no value or more than one value selected on the slicer

How many average days? = IF(HASONEVALUE('Days for Average'[Days]), VALUES('Days for Average'[Days Value]), 30) 

I added a measure to calculate how many calls we received on same day based on number of days selected on the slicer and it works great:

Total Number of calls on same week day = CALCULATE(SUM('Call - All'[CallAnswered]),FILTER('Calendar', 'Calendar'[Days From Today]>= -([How many average days?]*7) && 'Calendar'[Current Week Day] = 0 )) 

Now here is the actual issue/need help with

I added a page level filter saying "DaysFromToday" >= -210 days and I changed my above measure with ALL function so that I ignore the page level filter, I don't get the result as I expected. 

If I choose any value in my slicer below 30 days, it works but if I choose value above 30 days, it ignore ALL('Calendar') function in my measure. Not sure what I'm missing here, in my understanding, ALL function remove all the filter on calendar table and give me desired result.

Here is updated measure with ALL function

Total Number of calls on same week day = CALCULATE(SUM('Call - All'[CallAnswered]),All('Calendar'),FILTER('Calendar', 'Calendar'[Days From Today]>= -([How many average days?]*7) && 'Calendar'[Current Week Day] = 0 )) 

Sorry for long explanation, and I'm sure it is clear what I'm trying to do here but if there are more questions, please ask.

Thanks,

P

[Sean]

I have not read the entire post however I know ALL can drive you crazy

http://community.powerbi.com/t5/Desktop/ALL-Function-Mystery/td-p/107458

Do you have any columns involved that you have applied SORT BY COLUMN (meaning that sort not in their default order)

https://blog.crossjoin.co.uk/2015/12/15/power-bi-desktop-sort-by-column-and-dax-calculations-that-us...

[Phil_Seamark]

This is a pretty good go-to article (I use alot) which helps explain the various ALL, ALLEXECPT and ALLSELECTED filters and when each is useful to use

http://sqlblog.com/blogs/marco_russo/archive/2010/04/05/all-allexcept-and-values-in-dax.aspx

[parry2k]

Thanks gents, and appreciate the input but unfortuately I gone thru all these before posting my query.

Still trying to figure out what is the issue here. 

[parry2k]

@Sean none of the column I'm using which has sort by  other column in this specific Caclulation but yes there are fields in calendar table which are sorted by other columns

[parry2k · Accepted solution]

This is what seems to be fixed it, moved ALL(Calendar) after filter

Total Number of calls on same week day = CALCULATE(SUM('Call - All'[CallAnswered]),FILTER(All('Calendar'), 'Calendar'[Days From Today]>= -([How many average days?]*7) && 'Calendar'[Current Week Day] = 0 ))