Power BI

A lot of times you just to show values of rows from the current month compared to other specific time periods, like previous month, ytd, previous YTD, etc. The problem is, how do you determine the

Business users really like the option of comparing category members against the average for all. Let’s review how to accomplish this in DAX. Suppose you what to find out which months had an above a…

Hello,   I'm having some issues while creating my YTD metrics and thought that some of you could lend me a hand.   My case is the following: My database has information until march of 2016 (but it is constantly fed with new information in a monthly basis).   I need a metric that shows me the year to...

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

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

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

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

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

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

select MEASURE_NAME, EXPRESSION from $SYSTEM.MDSCHEMA_MEASURES

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

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

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

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

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