dax

This is actually the fifth post on Slicers, which means that it’s something that keeps coming up and that its something important to learn.

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

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!

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

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

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

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

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!