A quick update here on the football project (not really warranting a video).  The source db is awfully complex, and I’m in the process of wrapping my

Hello everyone, hope someone can help. I'm pretty new to Power BI and Dax so I'm just getting my head around the common data modelling concepts. I've created a few calculated columns and measures etc.   However, I'm wondering the best way to acheive the following.....   I have a datasource (guarante...

Hi,   I have a measure which uses   =-CALCULATE ( SUM ( Budget[Budget Data] ), FILTER ( ALL ( 'Sagecalcodes' ), INT('Sagecalcodes'[Year]) = YEAR(TODAY()) )) This only gives me 2017, so if someone goes back to 2016 it still shows 2017, how would I get what date is on the filter in...

UPDATE 2017-07-12: please note this article was written in 2010, there are now better ways to obtain the same result. Please read these articles: Physical and Virtual Relationships in DAX and Propagate filters using TREATAS in DAX PowerPivot supports

This chapter from "The Definitive Guide to DAX: Business intelligence with Microsoft Excel, SQL Server Analysis Services, and Power BI" covers evaluation contexts, which are the basis of all of the advanced features of DAX.

A filter argument in CALCULATE is always an iterator. Finding the right granularity for it is important to control the result and the performance. This arti

Hi,   I have two tables, a movements data and a raw data.   The movements data keeps track of which department the employee had been in a particular duration or to which supervisor he was reporting to in any given duration as well (the table might explain better, and it only show movement of one emp...

DAX Patterns is a collection of ready-to-use data models and formulas in DAX, which is the programming language of PowerPivot. Create your Excel data model faster by using a DAX pattern!

I am tracking KPIs for some sales reps, so I have a table of all the sales calls they made witih a column that shows what sales method they used (Cold Calls, Meeting, Etc.)  Each sales rep has chosen a single type of method, and and goal number of calls for that method they must make a week, so in a...

For me, Stephen Few has always been the guru of Data visualisations. Recently, while I was reading one of his paper, I came across most talked Bullet Chart and Highlighting a…

Last week, I was going through the PowerPivot forums as usual and was answering a question on How to handle count of data with “valid to/from” dates in DAX. This was a classic case of the “Events i…

It has been quite a while since I posted something and was already thinking of dusting up my tools. That was when I was going through the Power BI Community forums, and found an interesting questio…

I was working on a Power BI Model where I had a requirement where I wanted to use the Last value from an [Actuals] calculated measure as part of another calculated measure [Pro-rata Budget Selectio…

This article describes the IN operator in DAX, which simplifies logical conditions checking whether a certain value is included in a list of values or expre

In Power BI Desktop (as of February 2016) you have to use DAX to apply calculations over dates (such as year-to-date, year-over-year, and others), but you d

Ever wondered how to calculate outliers for a particular region, product, or customer group. Here Rob explains how to write the DAX to do just that!

By Matt Allington Intro from Rob Many of you already know of Matt Allington and his background as BI Director for Coca-Cola Asia Pacific.  Matt recently fl

This article explains how to use SUMMARIZECOLUMNS, which is a replacement of SUMMARIZE and does not require the use of ADDCOLUMNS to obtain good performance

If you do not have a full month with data, comparisons such as year-over-year (YOY) might require particular filters in order to do a like for like comparis

Here comes some long awaited comfort functions for part 2 of my easy P&L series, where you don't have to define the single accounts any more.

It’s is often handy to be able to define a table in line with your calculation. Apparently, you can do it in DAX using the following syntax: { value1, value1, value3} The statement above crea…

Finding Duplicates in PowerPivot – Within Current Table (HasDupeInThisTable) or Across Tables (HasDupeInTable2) I got a question on Twitter today wh

Hi guys.   I have a table with these columns:   Item No, Date, and Price   I want to add a fourth column, Latest Price. Latest Price looks at all rows with the same Item No, finds the latest Date out of all those rows, and gives me the Price from that row.     Item No Date Price Latest Price 12345 J...