Excel 2013

ARTICLE

Power Pivot Utils: a nice collection of VBA macros #powerpivot #dax

When you use Power Pivot, there are a number of operations requiring more than one click (or much more than just Excel): Creating a pivot table based on the current data model (at least 2 or 3 clicks) Create a table of existing measures (including its

ARTICLE

The ALLSELECTED function under the cover #dax #tabular #powerpivot #powerbi

I and Alberto Ferrari recently completed the writing of The Definitive Guide to DAX , and we spent months to correctly describe the internals of evaluation context in this language. There are many details that make data model working with both DAX and

ARTICLE

DAX Studio 2.2 released – tracing support for #powerpivot and much else! #dax

Thanks to Darren Gosbell we have DAX Studio 2.2. In this new release: Tracing (query plans and server timings) available for Power Pivot Basic implementation of Intellisense Connect to Multidimensional SSAS servers Support for multiple Power BI Desginer

ARTICLE

Possible SWITCH Optimization in DAX – #powerpivot #dax #tabular

In one of the Advanced DAX Workshop I taught this year, I had an interesting discussion about how to optimize a SWITCH statement (which could be frequently used checking a slicer, like in the Parameter Table pattern ). Let’s start with the problem. What

ARTICLE

DIVIDE vs division operator in #dax

Alberto Ferrari wrote an interesting article about DIVIDE performance in DAX. This new function has been introduced in SQL Server Analysis Services 2012 SP1, so it is available also in Excel 2013 (which still doesn’t have other features/fixes introduced

ARTICLE

Calculate New, Returning, Lost, and Recovered Customers in #dax

Calculating the number of new and returning customers is a recurring question. I would say this is a “classical” Business Intelligence problem, very common in marketing department. I worked on these problems with many customers, with small and large datasets,

ARTICLE

Connecting to #powerpivot from an external program (such as #Tableau)

Many people requested me how to connect to Power Pivot from an external program, without publishing the workbook on SharePoint or on Analysis Services Tabular. I always said it is not possible (for both technical and licensing reasons), but someone observed

ARTICLE

Using Added Columns in #DAX Queries

I had an issue in a query that reminded me a few details naming columns created by ADDCOLUMNS. When you specify a simple name in ADDCOLUMNS, you can use it later, for example in a SUMMARIZE or in a FILTER expression, such as in the following example (see

ARTICLE

Power Query now imports relationships #powerquery #powerpivot

The December release of Power Query preview (version 2.9.3502.261) introduced an interesting feature: when you import multiple tables in the data model, relationships existing between tables in the data source are automatically detected and added to the

ARTICLE

Dynamic Calculation of Inventory with #dax in #powerpivot and #ssas #tabular

I recently wrote an article on SQLBI describing the dynamic calculation of stock inventory in DAX. With this technique, you can avoid creating a snapshot fact table just to store the inventory stock for every day and every product, creating a table that