Archive
DAX III – Filtering Queries with CALCULATETABLE and FILTER
So far the queries that we have created in DAX have focused on the entire result set (with the exception of the start at clause in evaluate). Naturally, we would want to restrict the rowset in some way and this is the focus of this post – how to filter row-set tables in DAX queries. There are two ways to do this, firstly, we can use the CACULCUATETABLE syntax and secondly we can use a FILTER that covers the evaluate syntax.
BOL defines the function of CALCULATETABLE as “Evaluates a table expression in a context modified by the given filters” taking the form;
|
CALCULATETABLE(<expression>,<filter1>,<filter2>,…) |
There are many applications of how we can use this, however, let’s look at the first of reducing the rows returned, say for the first six months of 2000. We can evaluate this query like this.
|
evaluate |
|
|
One of the restrictions of using CALCULATETABLE to restrict rows is that the filter cannot be applied to calculated measures. So if we extended the filter arguments to include a condition on ‘sales_amount’, we would get an error.
|
evaluate |
|
A function ‘CALCULATE’ has been used in a True/False expression that is used as a table filter expression. This is not allowed. |
Note that the filter arguments in calculate table are applied to the underlying table regardless of the fields returned (projected or selected). So, if we wanted to sum for the first 6 months sales for the year 2000, we could simply remove the ‘dates’[month] field from the summarize and the calculation is applied only for Jan to Jun.
|
evaluate |
|
|
We could also include this restriction in the definition of the calculation and achieve the same results (see sum_amount2 below).
|
evaluate ) |
|
|
From the rowset point of view, we are still faced with the problem of how to restrict rows based on a calculated value. In order to do this, we can apply the FILTER function against the table. Filter returns a subset of the table and takes the form;
|
|
Consider yearly sales for the first six months of the year as below;
|
evaluate |
|
|
A filter can be applied to this rowset so that only sales over 50,000 are returned. Note that it is wrapped around the CALCUALTETABLE.
|
evaluate filter ) |
|
|
In these situations, it may be best to define the measure explicitly;
|
define measure ‘dates’[sum_amount] = calculate(sum(‘Sales’[amount])) evaluate filter ) |
DAX Studio (Beta) Released
The first installers for DAX Studio are released.
You can get them here
DAX II – Extending the use of Evaluate
In a previous post, I looked at evaluate as the method to return a table in a row-set form. However, the query syntax allows us to more precisely define the result set and this is the focus of this post.
Books on line specifies three optional arguments that can be used in query syntax to control, there are DEFINE, ORDER BY and START AT. The syntax follows the format;
|
[DEFINE { MEASURE <tableName>[<name>] = <expression> }
If your looking to get into DAX and the tabular model but not sure where to start or where dax fits in, theres a new whitepaper released. It gives a good overview and will get you started in no time. Retail analysis models often include a competing outlet indicator to improve the quality of the model. The indicator does this by allowing the user to manage the effect that is associated with partial trading periods. For example, the true / false indicator shows whether the store has traded for all available weeks in a month. This post looks at how the competing store indicator can be implemented using powerpivot and DAX. The Competing Store Definition There are a few definitions of competitive stores (and perhaps another post to follow). In this post, the definition of competitive is a store that trades for every week in the month. If the store has not traded for all weeks, it is not considered competitive and this permits the identification of sales that occurred when; The Model The underlying model considers a store dimension, date dimension, and sales data. The grain for sales is { store x day } à sales amount. Although, this implementation treats the sales relation {store x day} as unique, there is no requirement to do so. The dates table follows standard structure of a date dimension table (grain of day) with the fiscal calendar attributes aligned along a 445 Calender. The fields fiscal_fullweek_key has the format YYYYWW and fiscal_fullmonth_key has the format YYYYMM. These are standard implementations where YYYY refers to the century, WW refers to the week of year and MM refers to the month of year. The store table is a type 1 dimension (showing the ‘current’ state of the store). There is a strong argument to show the [competing store] as an attribute of the store dimension however, this would require a type 2 store dimension and intensive ETL. This is outside the management of the end user who consumes available data as it would require more intensive resources and relational constructs. Method The approach to determine whether the sale is competing or not is based on the comparison of the stores actual trading weeks in a month compared to the available trading weeks. When the two values are the same the sale is competitive, otherwise it is not. Additionally, note that this formula is applicable to each row of the sales fact because the sales fact is the only table that combines both store and date data and can therefore be used as the auto-exists cross-join of stores and dates. For each row in the sales fact, we must determine; In order to improve readability of the formula the sales table has been extended to include a field full_month_key (formula = RELATED(fiscal_fullmonth_key)) and full_week_key (formula = RELATED(fiscal_fullweek_key)) Formula 1 – Available Trading Weeks in Current Month (weeks_in_month) The available weeks in the current month are the distinct count of weeks for the related month. We can achieve this by removing the filter context of the dates table and reapplying it so that the row sales month equals the dates month (below) DISTINCTCOUNT(Dates[fiscal_fullweek_key]) , filter(all(Dates), Sales[fullmonth_key]=Dates[fiscal_fullmonth_key]) ) If the sales table did not include the fullmonth_key field, we could use the related function directly in the formula. DISTINCTCOUNT(Dates[fiscal_weeks_key]) , filter(all(dates), RELATED(Dates[fiscal_fullmonth_key])=Dates[fiscal_fullmonth_key]) ) Alternatively, we can remove the row filter context of the dates table to the extent that it only includes the current month. This is done with the ALLEXCEPT function so that the dates table filters are removed except for the listed column restrictions (as below) DISTINCTCOUNT(Dates[fiscal_fullweek_key]) , ALLEXCEPT(Dates,Dates[fiscal_fullmonth_key]) ) Formula 2 – Weeks Traded in the Current Month (weeks_sales_in_month) To determine the number of weeks that a store traded for (in the current month), we use a similar restriction but apply it to the sales table. That is, count distinct weeks after we remove the current row filter context and reapply it based on the current month of the rows sale date. DISTINCTCOUNT(sales[week_key]) , ALLEXCEPT(sales,sales[store_key], sales[fullmonth_key]) ) Formula 3 – Continuing Sales The final (and only visible formula) is a simple if function that compares the trading weeks to those available for a true/false output. =if([weeks_in_month]=[weeks_sales_in_month], “Yes”, “No”) Final Model The final schema for the model is shown below. The measure [Sales Amount] is simply the sum of the sales_amount field. In a previous post I looked at the built in DAX Total to date functions (TotalYTD, TotalMTD, TotalQTD) which sum calendar periods to an end date. The YTD function is somewhat flexible because it allows a year end date to be passed as an optional argument in the function. However, the end date is static in the formula, that is, the same year end date is applied must be applied to all years (also indicating the start of the next year is the day after the prior year ends). The 445 calendar contravenes this requirement because the yearend date (and year start date) is not consistent between years. The 445 Calendar The Formula The calculation works by simply removing the current date filter context and then reapplying it so that the year is restricted to the current fiscal year and the date is less than the maximum date for any filter (ie row, column, filter axis) that is applied. The use of max for the fiscal_year_key is simply to return the current year as a scalar. An alternative approach that deals with text running balances can be found at Javier Gullen‘s blog. DAX has three periods to date functions which easily allow the creation of common the common measures (Month To Date, Quarter To Date and Year To Day). This post looks at period to date calculations and member sorting in the BISM tabular model. Sorting After sorting, members appear as expected in both the project design and deployed project. Period to Date Calculations Period to date can be easily created using the DAX functions TOTALYTD, TOTALMTD, TOTALQTD. Note that these functions are not dependent on sort order but on a related date field. The general format of the function is TOTALPPP ( scalar_function , time_column) where the time_column is a primary related date field. To show YTD Values for SalesAmount we use the formula: Sales YTD:=TOTALYTD(sum(FactResellerSales[SalesAmount]), DimTime[TimeDate]) Also note that measures (as they would be thought of in SSAS) must be added to the ‘Measure Grid’. If they are not, measures are not seen in the cube. The table FactResellerSales has columns hidden from client tools (so that no dimension members are created) and contains the measures [Sales] and [Sales YTD] (below). Browsing the cube shows data (sorted) with YTD values as expected. Unlike SSAS, the tabular model will show the [YTD] measure defaulting to the last year when the (related) time table is not used. MISC The technet reference for these functions can be found at YTD , QTD , MTD . |
