DAX

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

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

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

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

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

In one of my previous posts link

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

YTD is easy with

Guest Post by Jeff Lingen [LinkedIn] We don

A common request from business users is

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

I was studying how I could implement time intelligence using DAX from the masters [1]

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

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

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...

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

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

Introduction A small blogpost about backing up your DAX Measures of PowerPivot in Excel 2013. Although PowerPivot is a great tool, it can ...

select MEASURE_NAME, EXPRESSION from $SYSTEM.MDSCHEMA_MEASURES

SQLBI is a blog dedicated to building Business Intelligence solutions with SQL Server. You can follow me on Twitter: @marcorus The latest version of Power Pivot Utils v 1.05 has a new feature that

PeriodCurrentMTDQTDYTDPY CurrentPY MTDPY QTDPY YTDYOYYOY%YOY YTDYOY% YTDPM CurrentMoMMoM%PQ CurrentQoQQoQ%

This should work: =IF(IFERROR(SEARCH("Chrome",[SoftwareName]),-1)

We Want to Flag Rows in our Companies Table (on left) When TheyContain a Keyword from our MatchList Table (on right) These are a few of my favorite

This DAX-VizArt-Wizard vizualizes dependencies between your DAX measures, shows the definition of all related measures and shows differences between the measures of 2 models/versions. This works for Power Pivot, Power BI (…) Read more

“Excuse me, sir, you don't need 20 men, you need Miller Banfield.  He's an expert with explosives Excel, sir. He's probably the best in England

This is a collaborative blog post by  and  resulting from the forum discussion topic “Aggregating Duration/Time”. http://community.powerbi.com/t5/Desktop/Aggregating-Duration-Time/m-p/13350/highlight/true#M3358   Introduction Both Excel and DAX lack support for a true time duration data type makin...

This is a collaborative blog post by  and  resulting from the forum discussion topic “Aggregating Duration/Time”. http://community.powerbi.com/t5/Desktop/Aggregating-Duration-Time/m-p/13350/highlight/true#M3358   Introduction For users coming from an Excel background, it is easy to fall into the t...