More on the CALCULATE function…

5 באוקטובר 2010

2 תגובות

A bit more information regarding the new CALCULATE function in DAX. I’ve described what this function does in a previous post, and I want to expand on how to treat this function in a PowerPivot report. Consider the following report:

 

GeneralReport

 

We have Order Quantity sales ([Order Quantity Total]) and two calculated measures, which we are going to discuss shortly. We have the promotions dimension in the Y axix and 2 slicers: Product Category Name and Calendar Year.

The DAX syntax for the calculated measures is as follows:

Order Quantity in 2003:

CALCULATE(SUM(FactInternetSales[OrderQuantity]), DimDate[CalendarYear]=2003)

Here we aggregate the [Order Quantity] column and apply a filter to look only at records from the calendar year 2003.

Order Quantity for Accessories product category:

CALCULATE(SUM(FactInternetSales[OrderQuantity]), DimProductCategory[EnglishProductCategoryName]="Accessories")

We again aggregate the [Order Quantity] column and apply a filter to look only at records that relate to a sale of a product that belongs to the “Accessories” product category group.

Note also that we currently have not applied any selections (filters) on our available slicers.

The question comes to mind: if we now apply a filter on the Calendar Year slicer and say, choose the year 2002. What will we be seeing in the [Order Quantity in 2003] column? This is what actually happens:

 

2002Filter

 

You will see that the calculated measure [Order Quantity in 2003] displays the same data it displayed, as if the 2002 calendar year filter hasn’t been applied. The rest of the columns display filtered data for the year 2002. What we need to understand is that the calculated measure is evaluated independently of the slicer, but just in case the dimension used for filtering is the same dimension used in the slicer. You’d see that the second calculated measure that filters data by product category is reflecting the calendar year 2002 filter and displays no data.

A similar test using the Product Category Name slicer will show the same results:

 

BikesFilter

 

We now filter by product category Bikes, but the calculated measure that displays data filtered by the Accessories category displays the same data as if no slicer filter has been applied.

It is important to understand when these calculations are taking place, so your report will actually display the data you want it to display.

 

HTH,

-Yaniv

(This is post number 24 for week number 45 of my SQL blog challenge)

Technorati Tags: ,,
הוסף תגובה
facebook linkedin twitter email

כתיבת תגובה

האימייל לא יוצג באתר. שדות החובה מסומנים *

2 תגובות

  1. David Hager6 באוקטובר 2010 ב 4:29

    It's a little hard to tell how the measure columns calculated, since they are cut off in the screenshots.

    הגב