PowerPivot is a wonderful product, but its editor is its weakest point: Making expressions easy to read is difficult! This blog shows how to download and get started with DAX Studio, a separate Excel

It's all too easy in SSAS Tabular to suffer from measure overload: A typical set of date-based measures created in the course of setting up a tabular model. Wouldn't it be nice if you could group

The newly released DAX studio is an excellent tool to develop and troubleshoot DAX calculations as it provides a way to visualize tables as they are internally represented during the computation. The

Yesterday was a terrific day for all of Microsoft Power BI fans. Microsoft released updates for Power BI Service, Power BI Mobile and Power BI Desktop (with an unbelievable 44 new features)

In the 2015 September update, Power BI introduced calculated tables, which are computed using DAX expressions instead of being loaded from a data source. This article shows the usage of calculated

If you want to collect your regularly loaded data without overwriting it or create a load log that writes the load activites with a

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

YoY% = var Sales = [Sum of SalesAmount]var SalesLastYear=CALCULATE([Sum of SalesAmount], SAMEPERIODLASTYEAR(‘Calendar'[Date]))return if(Sales, DIVIDE(Sales – SalesLastYear, Sales))

I don’t normally blog about Excel topics outside of Power Query and Power Pivot, but I think anyone who has ever done any serious work in Excel has found how difficult it is to concatenate more tha…

This post solves a tricky issue of removing offset duplicates or, in other words, removing items from a list that exist not only in a different column, but also on different rows. Problem History This data format is based on … Continue reading →

For this reason, calling LASTDATE when you have a row context (as it is the case in the FILTER of the original Last Balance formula) generates a context transition, and the internal MAX function only consider the date of the current row in the accounts table during the FILTER in Last Balance.Remember: LASTDATE is a table function, which is useful to use in a filter argument of CALCULATE, whereas MAX is a scalar function, which cannot be used in a filter argument. For this reason, you can write:Units LastNonBlank := CALCULATE ( SUM ( Inventory[UnitsBalance] ), LASTNONBLANK ( 'Date'[Date...

DAX introduced a GROUPBY function that should replace SUMMARIZE in some scenarios. This article describes how to use GROUPBY in nested grouping scenarios an

Intro Data Analysis Expressions (DAX) In Excel 2010 PowerPivot DAX: Excel data analysis functions to construct dynamic analysis custom pivots on front end

Some time ago, I blogged about using a date calculation dimension in a multidimensional cube, and how easy it makes doing time intelligence in a multidimensional model. 2014 seems to have started out

A common request from business users is

Guest Post by Jeff Lingen [LinkedIn] We don

Yet another new M function for you this week: the Table.Schema() function, which returns information about the columns in a table. There’s some fairly detailed documentation about what it returns h…

YTD is easy with

DAX queries allow the user to retrieve data defined by a table expression from the xVelocity in-memory analytics engine (VertiPaq). The user can create measures as part of the query; these measures

In one of my previous posts link

The New and Returning Customers pattern dynamically calculates the number of customers with certain behaviors based on their purchase history. This calculation can be computing-intensive and, thus, a

The Handling Different Granularities pattern is a technique to show and hide measures depending on the level of granularity of the underlying data. For example, you do not want to display a measure

Introduction By now you have likely heard a little about Power Query (formally known as Data Explorer).

The Parameter Table pattern is useful when you want to add a slicer to a PivotTable and make it modify the result of some calculation, injecting parameters into DAX expressions. To use it, you must

Football data source tables - PowerPivotPro via Instapaper http://www.powerpivotpro.com/2009/10/football-data-source-tables/

SQLBI - Marco Russo : How CALCULATE works in DAX via Instapaper http://sqlblog.com/blogs/marco_russo/archive/2010/01/03/how-calculate-works-in-dax.aspx

Dynamic Charting In Power Pivot - PowerPivotPro via Instapaper http://www.powerpivotpro.com/2014/08/dynamic-charting-in-power-pivot/

Writing a RANK measure (and living to tell the tale) - PowerPivotPro via Instapaper http://www.powerpivotpro.com/2010/03/writing-a-rank-measure-and-living-to-tell-the-tale/

ALL() Revisited - PowerPivotPro via Instapaper http://www.powerpivotpro.com/2010/02/all-revisited/

Resolving Many to Many relationships leveraging DAX Cross Table Filtering via Instapaper https://gbrueckl.wordpress.com/2012/05/08/resolving-many-to-many-relationships-leveraging-dax-cross-table-filtering/