Modifying Query Context with ALL()?

Anything related to PowerPivot and DAX Formuale
CarbonChauvinist
Posts: 9
Joined: Thu Jun 15, 2017 6:06 am

Modifying Query Context with ALL()?

Postby CarbonChauvinist » Thu Jun 15, 2017 6:57 am

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

fsales.PNG
Simplified Table Diagram
fsales.PNG (28.68 KiB) Viewed 1143 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:

Code: Select all

Number of Sales Days in Year:=DISTINCTCOUNT(‘fSales'[creation_date])


That measure gives me the correct number of sales days in the fSales table which I’ve verified by filtering the fSales tables to single years (i.e. 2015) and verifying the distinct count is correct.

Now, when creating the pivot table report I simply use the 'dCalendar'[psg_dates] as my row label and then a measure I’ve created for TotalSales in the values section. All good so far.

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

incorrect_sales_days_measure.PNG
Number of Sales Days in Year Measure returns incorrect value when pivoted off a single date from the calendar table ('dCalendar'[psg_dates])
incorrect_sales_days_measure.PNG (6.28 KiB) Viewed 1143 times

It does work correctly if I use the Year column from the dCalendar table as my rows (see screen grab)

correct_sales_days_year_context.PNG
Measure works correctly when based off years from the calendar table ('dCalendar[Year])
correct_sales_days_year_context.PNG (4.18 KiB) Viewed 1143 times

How would I use the ALL() function to remove the filter context provided on the month and day, therefore returning an accurate DISTINCTCOUNT() of all the ‘fSales'[creation_date] values for the year?

MattAllington
Posts: 898
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Modifying Query Context with ALL()?

Postby MattAllington » Thu Jun 15, 2017 8:12 am

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.

Read my best practices here http://exceleratorbi.com.au/best-practi ... -power-bi/

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.



If you want a more in depth explanation of Filter context, watch my video on Evaluation Contexts from the MDIS this week. http://exceleratorbi.com.au/my-mdis-presentations/
Matt Allington is Professional Self Service BI Consultant, Trainer and Author of the book "Learn to Write DAX". You can hire me at http://Exceleratorbi.com.au
http://exceleratorbi.com.au/what-is-power-pivot/
http://xbi.com.au/learndax

CarbonChauvinist
Posts: 9
Joined: Thu Jun 15, 2017 6:06 am

Re: Modifying Query Context with ALL()?

Postby CarbonChauvinist » Sat Jun 17, 2017 3:15 am

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:

Code: Select all

SalesDays:=CALCULATE(DISTINCTCOUNT('fPSGSales'[creation_date]), ALLEXCEPT('dCalendar', 'dCalendar'[Year]) )

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):
salesdays_measure_returns_for_all_years.PNG
Returns sales days across all years instead the year for the current row in the visual.
salesdays_measure_returns_for_all_years.PNG (6.17 KiB) Viewed 1115 times

And here's a quick visual showing that all the sales days across the years is the value being returned for the measure:
all_sales_days_across_all_years.PNG
Visual showing sales days by year
all_sales_days_across_all_years.PNG (3.51 KiB) Viewed 1115 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):

Code: Select all

CorrectSalesDays:=CALCULATE(DISTINCTCOUNT('fPSGSales'[creation_date]), FILTER(ALL('dCalendar'), 'dCalendar'[Year]=????) )

MattAllington
Posts: 898
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Modifying Query Context with ALL()?

Postby MattAllington » Mon Jun 19, 2017 7:24 am

You need a filter on Calendar[Year] if you want ALLEXCEPT to work. If there is no filter on Calendar[Year], then ALLEXCEPT will be the same as ALL.
Matt Allington is Professional Self Service BI Consultant, Trainer and Author of the book "Learn to Write DAX". You can hire me at http://Exceleratorbi.com.au
http://exceleratorbi.com.au/what-is-power-pivot/
http://xbi.com.au/learndax

CarbonChauvinist
Posts: 9
Joined: Thu Jun 15, 2017 6:06 am

Re: Modifying Query Context with ALL()?

Postby CarbonChauvinist » Wed Jun 21, 2017 5:46 am

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
sales_days_correct.PNG (26.77 KiB) Viewed 1013 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.

MattAllington
Posts: 898
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Modifying Query Context with ALL()?

Postby MattAllington » Thu Jun 22, 2017 7:54 am

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
Matt Allington is Professional Self Service BI Consultant, Trainer and Author of the book "Learn to Write DAX". You can hire me at http://Exceleratorbi.com.au
http://exceleratorbi.com.au/what-is-power-pivot/
http://xbi.com.au/learndax

Oxenskiold
Posts: 30
Joined: Tue Jan 05, 2016 10:38 pm

Re: Modifying Query Context with ALL()?

Postby Oxenskiold » Thu Jun 29, 2017 7:55 am

Hi CarbonChauvinist,

you can use this simple measure instead of the one you show. It has the advantage of not referring to hardwired years (2016, 2017 etc …) :

Code: Select all

Number of Sales Days in Year :=
CALCULATE ( [Sales days], VALUES ( 'dCalendar'[Year] ), ALL ( 'dCalendar' ) )


The measure will react to the date you have on the axis of the pivot table and return the number of sales days in the year of that particular date

Hope you can use the measure in your future work.

PS: I'm a big fan of the table naming rule that Matt is mentioning if possible.

Best regards
Jes.

CarbonChauvinist
Posts: 9
Joined: Thu Jun 15, 2017 6:06 am

Re: Modifying Query Context with ALL()?

Postby CarbonChauvinist » Fri Sep 01, 2017 3:45 am

Oxenskiold wrote:Hi CarbonChauvinist,

you can use this simple measure instead of the one you show. It has the advantage of not referring to hardwired years (2016, 2017 etc …) :

Code: Select all

Number of Sales Days in Year :=
CALCULATE ( [Sales days], VALUES ( 'dCalendar'[Year] ), ALL ( 'dCalendar' ) )


The measure will react to the date you have on the axis of the pivot table and return the number of sales days in the year of that particular date

Hope you can use the measure in your future work.

PS: I'm a big fan of the table naming rule that Matt is mentioning if possible.

Best regards
Jes.


Thanks so much for this Oxenskiold and yes I've made sure to rename the tables to more friendly, understandable names.


Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: No registered users and 1 guest