DAX

The RANKX function in Power BI might have an unexpected behavior when applied to a column that has a specific sort order in the data model. This article exp

Hi,   I recently wrote a DAX measure based on some help I got on this forum. However I now see that these measures are very consuming in terms of memory and slow my report down way too much. I have a table with the following structure: I have one line per person giving me the name of that person and...

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!

In any version of DAX, you can aggregate data by grouping one or more columns using SUMMARIZE and/or ADDCOLUMNS. In Excel 2016, Power BI Desktop, and Analys

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

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

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…

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

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!

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

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 describes the IN operator in DAX, which simplifies logical conditions checking whether a certain value is included in a list of values or expre

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

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…

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…

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…

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!

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

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

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.

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

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

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

This article describes how table expansion and filter context propagation are important DAX concepts to understand and fix small glitches in DAX expressions