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  Read more

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  Read more

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  Read more

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  Read more

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  Read more

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,  Read more

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  Read more

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  Read more

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  Read more

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  Read more