I’ve been tasked with putting together a report that lists the top N sales days in a given time period which I’ve been able to do successfully. This is a simple report with only two tables, the fact sales table (fSales) and a calendar table (dCalendar) (see attached screen grab for reference).

Simplified Table Diagram

fsales.PNG (28.68 KiB) Viewed 1456 times

I’m simply trying to create a measure that shows the total number of sales days for the entire year for the top sales days shown and I’m struggling. I know that one would need to use the ALL() function to remove query and/or filter context from a given measure, but I can’t figure out how in the world to make it work.

For instance the top sales day in my example is 5/30/2015. The measure I’ve created for counting the number of sales days is fairly straight forward:

Firstly let me complement you on providing clear information. If you help me to help you, you will definitely get more help from me .

Now, first some advice. Give your tables a name that mean something to your end users. eg don't call it dCalendar but call it Calendar. Don't call it fSales but call it Sales. This approach you are using is a hang over from IT built solutions where the tables are not visible to the end user. But your tables are visible to the end user, and they will have no idea what fSales means.

The problem becomes the “Number of Sales Days in Year” measure when dragged into this pivot table only returns a value of one (1) for each row since I’m pivoting off individual days (see screen grab). It does work correctly if I use the Year column from the dCalendar table as my rows (see screen grab)

One thing for sure - the table always delivers the correct answer. It may not be what you want, but it will definitely be the right number based on your formula.

The visual will filter your data model and then do the calculation. So you are first filtering on a single day and then doing the calc. That is why you get 1. If you want the number of days in a year, you need to remove the filter on everything other than the year (assuming you have that in your calendar). So something like this.

Wow, thanks so much for the helpful reply Matt; very much appreciated!

I'll definitely take to heart your best practice recommendations re: table naming conventions moving forward, thanks again. I also watched both videos in your link, and will be re-watching the one dealing with evaluation context at least a couple more times for sure.

I'd actually tried using the ALLEXCEPT() formula to remove all filters on the Calendar table except for the year filter, unfortunately it returns the total number of sales days across all the years and not just the actual year in question from the visual's row label.

I'm not sure what I may be doing wrong. I should be using the 'dCalendar'[psg_dates] field (which is marked as Date Table in PowerPivot) as the row label for my visual as opposed to the 'fSales'[creation_date] field correct? And that's because, as I understand it, filters only flow from the lookup/dimension tables to the fact tables (from the one to the many).

Just to be as clear as possible the new measure I've created to try and get all the sales days in the year is as follows:

However, when placed in the visual it's returning all sales days across all years instead of limited to the year in question for the current row label in the visual (see screen grab below for reference):

Returns sales days across all years instead the year for the current row in the visual.

And here's a quick visual showing that all the sales days across the years is the value being returned for the measure:

Visual showing sales days by year

all_sales_days_across_all_years.PNG (3.51 KiB) Viewed 1428 times

Is there a way that I can pull the applicable year from the visual's row label and then simply have the measure constructed as so (where ???? refers to the year value pulled from the visual's row label):

Thanks for the help and time Matt, I do appreciate it. Based on your clues I was able to get the following to work for my needs:

Which produces the visual pasted below when used in a pivot table:

sales_days_correct.PNG (26.77 KiB) Viewed 1326 times

I'm not totally sure why I need the HASONEFILTER() function, but in my rounds of testing I couldn't get my measure to work as needed without it. I've just ordered your book from Amazon and can't wait to start digging through.

values can return a table or a value (if there is only 1 row in the table). If it returns a table, then the SWITCH function will fail (switch needs a value not a table). So you need to protect the formula so that it is guaranteed to return a value, not a table. That is what HASONEFILTER is effectively doing. You could use MAX(Calendar[Year]) instead and get rid of the IF statement given MAX will only ever return a value, not a table.

Also, do yourself (and your users) a favour and get rid of the d and f in front of your tables. These are technical concepts that don't mean anything to end users. Do you want them to go to the dCalendar table or the Calendar table? The latter is better for users