Using DateDiff() To Calculate Time Intervals In DAX

One of the most useful new additions to DAX in Excel 2016 and the Power BI Designer is the DateDiff() function. It does exactly what you would expect: calculate the amount of time in between two dates, and express that value as either seconds, minutes, hours, days, weeks, months, quarters or years.

Here’s a very simple table of dates:


With this table loaded into the Power BI Designer, you can add new calculated columns to the table by clicking the New Column button on the ribbon. Here are two calculated column definitions that give the number of days and the number of years between the Start Date and the End Date on each row:

DayDurationColumn = 
DATEDIFF(MyTable[Start Date], MyTable[End Date], DAY)

YearDurationColumn = 
DATEDIFF(MyTable[Start Date], MyTable[End Date], YEAR) 

The output is pretty much what you’d expect:


It is of course also possible to create measures that use the DateDiff() function to, for example:

YearDurationMeasure = 
FIRSTDATE(MyTable[Start Date]), 
LASTDATE(MyTable[End Date]), 


All very straightforward, then, and much easier than having to calculate these values yourself.

Using SelectColumns() To Alias Columns In DAX

A few years ago I wrote this post on how to alias columns in a table in DAX, using a combination of AddColumns() and Summarize(). The good news is that in Excel 2016/the Power BI Designer/SSAS Tabular 2016 there’s a new DAX function specifically for this purpose: SelectColumns(). Here’s an example of how it can be used:

Imagine you have the following source table, called Products:


You can write a DAX query to get all the rows and columns from this table like so:


Here’s the output of that query in DAX Studio (and remember, DAX Studio can connect to data loaded into the Power BI Designer, which is what I’m doing here):


You can alias the columns in this table using SelectColumns() very easily, like so:

    "Column One", Products[Product],
    "Column Two", Products[Colour]

Here’s the output:


The syntax for SelectColumns() is straightforward: the first parameter is a table expression, and after that there are pairs of parameters consisting of:

  • A new column name
  • An expression returning a column from the table given in the first parameter

As you can see in the output of the query above, I’ve renamed the Product column “Column One” and the Colour column “Column Two”.

This means I can now crossjoin a table with itself without needing to worry about conflicting column names, like so:

        "Column One", Products[Product],
        "Column Two", Products[Colour]


One other interesting thing to note about SelectColumns() is that it allows you to do projection in a DAX query easily – as Marco notes here, it was possible before but it wasn’t pleasant. For example, the query:

    "Just Colour", Products[Colour]



Notice how there are three rows in the output here and that the value Green occurs twice. If you’re a true DAX afficionado, you might get excited about that.

Documentation For New Excel 2016 DAX Functions

Microsoft has published documentation for the new DAX functions in the Excel 2016 preview here:

There’s a lot of detail, including examples (although the ConcatenateX() page isn’t live at the time of writing – but I’ve blogged about that already), so it’s well worth reading through.

NaturalInnerJoin And NaturalLeftOuterJoin DAX Functions In Excel 2016

Continuing my series on new DAX functions in Excel 2016, here are two more: NaturalInnerJoin() and NaturalLeftOuterJoin(). Both do pretty much what you’d expect.

Consider the following two tables in an Excel worksheet, called ColourFruit and FruitPrice:


With these tables loaded into the Excel Data Model as linked tables, the next step is to create a relationship between the tables on the Fruit column:


Both functions only work with two tables that have an active relationship between them, and both take two tables from the Excel Data Model as parameters. Once you’ve done that you can use these functions in a DAX query.

The queries

evaluate naturalinnerjoin(ColourFruit,FruitPrice)


evaluate naturalinnerjoin(FruitPrice,ColourFruit)

…both perform an inner join between the two tables on the Fruit column and both return the same table:


The query

evaluate naturalleftouterjoin(ColourFruit,FruitPrice)



The query

evaluate naturalleftouterjoin(FruitPrice,ColourFruit)



For NaturalLeftOuterJoin() the table given in the first parameter is on the left-hand side of the left outer join, so all rows from it are returned, whereas the table in the second parameter is on the right-hand side of the join so only the matching rows are returned.

ConcatenateX() DAX Function In Excel 2016

This is the first of many posts on the new DAX functions that have appeared in Excel 2016 (for a full list see this post). Today: the ConcatenateX() function.

The mdschema_functions schema rowset gives the following description of this function:

Evaluates expression for each row on the table, then return the concatenation of those values in a single string result, separated by the specified delimiter

Its signature is:

CONCATENATEX(Table, Expression, [Delimiter])

It’s easier to understand what it does using a simple example though. Consider the following table on a worksheet in Excel 2016:


When you add this table to the Excel Data Model (I called the table Sales) you can add the following measure:

Purchasing Customers:=

If you then use this measure in a PivotTable, you see the following:


As you can see, the measure returns a comma-delimited list of all of the customers who have bought each product. Very useful…

Point-In-Time Dimension Reporting In DAX

Before I start, I have to state that the technique shown in this post isn’t mine but was developed by my colleague Andrew Simmans, who has very kindly allowed me to blog about it.

Over the last few months I’ve been working on an SSAS Tabular project that has not only presented some interesting modelling challenges, but has shown how DAX can offer some new and interesting solutions to these challenges. Consider the following scenario: a supermarket sells products, and we have a fact table showing sales of products by day. Here’s some sample data:


To complicate matters, each product has one product manager but product managers for particular products change from time to time. Normally this might be solved by adding the product manager name to the Product dimension table and implementing a Type 2 Slowly Changing Dimension. In this case, though, we want something slightly different: instead of seeing sales attributed to the product manager who was in charge of the product at the time of the sale, and therefore seeing sales for the same product attributed to different product managers on different dates, we want to attribute all sales for a product to a single product manager but be able to use a second date dimension to be able to determine the point in time, and therefore the product manager in charge of each product at that point in time, that we want to report as of. To put it another way, we want to be able to find the state of a dimension on any given date and use that version of the dimension to do our analysis.

For example, we have the following table showing which product manager was in charge of each product at any given point in time:


Between January 1st 2013 and January 3rd 2013 Jim was the product manager for Orange, but from January 4th 2013 onwards Rob took over as product manager for Oranges; Fred was the product manager for Apples the whole time. We want a PivotTable that looks like this when we choose to report as of January 2nd 2013:


Notice how Jim is shown as the product manager for Oranges. If we wanted to report using the managers as of January 5th 2013, we would want to see Rob shown as the product manager for Oranges like so:


The solution to this problem involves a combination of two DAX techniques that have already been blogged about quite extensively and which I’d encourage you to read up on:

  • Many-to-many relationships, in this case the solution developed by Gerhard Brueckl, described on his blog here.
  • ‘Between’ date filters, which I wrote about recently but which Alberto has recently improved on in his must-read white paper here.

Here are the table relationships I’ve used for the sample scenario:


I’ve added a second date table called ReportingDate which contains the same rows as the Date table shown above; note that it has no relationship with any other table.

This problem is very similar to a many-to-many relationship in that a product can have many managers across time, and a manager can have many products. Indeed we could model this as a classic many-to-many relationship by creating a bridge table with one row for each valid combination of product and manager for each possible reporting date; on my project, however, this was not a viable solution because it would have resulted in a bridge table with billions of rows in it. Therefore, instead of joining the ReportingDate table directly to the ProductManager table, we can instead filter ProductManager using the between date filter technique.

Here’s the DAX of the Sum of Sales measure used in the PivotTables show above:

Sum of Sales:=





FILTER(ProductManager, MIN(ReportingDate[ReportingDate])>=ProductManager[StartDate] 


IF(ISBLANK(ProductManager[EndDate]), TRUE(), 






This is not necessarily the best way to write the code from a performance point of view but it’s the most readable – if you need better performance I recommend you read Alberto’s white paper. What I’m doing is this:

  • Only return a value if a single reporting date is selected
  • Filter the ProductManager table so only the rows where the selected reporting date is between the start date and the end date are returned, ie we only get the rows where a manager was in charge of a product on the reporting date
  • Use the filtered ProductManager table to filter the main fact table using the Calculate() function, in exactly the same way that you would with a many-to-many relationship

You can download my sample workbook here.

Defining DAX Measures In The With Clause Of An MDX Query

It’s a little-known fact (but certainly not completely unknown – it was mentioned in Marco, Alberto and my SSAS Tabular book I think) that you can define measures using DAX in the WITH clause of an MDX query. This means you can write queries like the following against an SSAS Tabular model:

measure ‘Date'[Demo Calc] =

select {measures.[Demo Calc]} on 0,
[Date].[Calendar Year].members on 1
from [Model]


The official documentation, such as it is, is here:

Unfortunately you can’t use it from Excel 2013 using the new ‘create calculated measure’ functionality; I also talked to the nice people behind OLAP PivotTable Extensions and there are some very good reasons why they can’t support this either.

What use is this then? You’re only going to be able to use it in scenarios where you control the generation of the MDX on the client side, such as SSRS reports, which may not be all that often; in fact, in these situations you might be better off writing the whole query in DAX. It’s only going to be useful when you need the power of MDX and DAX in the same query. For example, you might want to take advantage of DAX’s superior ability to detect multiselects, but write all your other calculations in MDX. I’m clutching at straws here though! Still, it’s an interesting thing to know about. Please leave a comment if you can thing of a situation where you can use it…