Press "Enter" to skip to content

Category: DAX

DAX Financial Functions

Bill Pearson begins a series on financial functions inside DAX:

As a part of this introduction, you’ll have an opportunity to examine how each function can be employed to support business requirements of the sort that your hypothetical colleagues encounter routinely, and, for the most part, accomplish with Microsoft Excel, in meeting regular business requirements. You’ll learn the purpose of each function, and then undertake a practice example with each that demonstrates how it interacts with a small loan data set, via a calculation that you construct. Moreover, you will:

– Examine the syntax involved in exploiting the function.
– Undertake an illustrative example of the use of the function in a practice exercise.
– Review a brief discussion of the results you obtain in the steps of the practice example.

This is a thorough opening article.

Leave a Comment

MAX Versus LASTDATE in DAX

Alberto Ferrari explains why you might want to use MAX() instead of LASTDATE() to find the latest date in a table with DAX:

Many DAX newbies use LASTDATE to search for the last date in a time period. Or they use NEXTDAY to retrieve the day after a given date. Although these functions do what they promise, they are not intended to be used in simple expressions. Instead, they are table functions designed to be used in time intelligence calculations. Using them the wrong way leads to inefficient code. Moreover, using these functions in ways they were not designed for is a telltale sign that the developer still does not grasp certain details of DAX.

In this article, we elaborate on the topic in order to understand what these time intelligence functions do; we also want to understand the reason why it is so easy to confuse them with simple math over dates. We want to elaborate on this topic through examples. Therefore, instead of starting with boring theory, we start by looking at a calculation that – although it works perfectly fine – is inherently wrong.

Read on for that explanation.

Leave a Comment

Hardcoding Calculation Groups in a Measure

Kasper de Jonge works around a limitation with the Power BI UI and calculation groups:

AS we have seen calculation groups are great :). It offers amazing flexibility and is extremely easy to maintain. But sometimes it doesn’t do what you want due the limitations of the visuals. Let’s say I want to have a visual that shows me the sales of current year and sales Previous year on different axis (let’s say as line).

You would create something like the visual below where you want to use the same measure but apply different calc groups for each measure. But unfortunately, below visual is not as we want it to be.

But Kasper has us covered with a bit of DAX, so check that out.

Comments closed

DAX Patterns: Second Edition

Marco Russo announces a second edition of DAX Patterns:

Great news! Just one year after releasing the second edition of The Definitive Guide to DAX, we just published a new website, a new book, and a new collection of videos: the second edition of DAX Patterns!

DAX Patterns is a collection of patterns in DAX for Power BI, Analysis Services Tabular, and Power Pivot for Excel. The first edition of DAX Patterns dates back to the end of 2014, and it was based on Power Pivot for Excel. Since then, DAX has evolved with many useful features. Most importantly, Power BI hit the market, and the number of users adopting DAX grew at an exponential rate. When we published the first edition of this book, Power BI had not even been announced yet. Today, most DAX users create a Power BI solution. The new edition of DAX Patterns is thus based on the tool you love: Power BI.

The book looks to be quite useful, and you can get an idea if this content is right for you from the DAX Patterns website. What’s crazy is that they’re offering everything in the book on the website for free, but I’d suggest that if you pick up enough good info from the site, give back by buying a copy of the book or videos.

Comments closed

Filtering out Blanks in MEDIANX with DAX Studio

Matt Allington continues a series on blanking out:

This article is a follow on from last week. I recommend you go back and read the article first if you missed it, but in summary, I want to write a measure (not a calculated column) that will return the median sales of products while excluding the products with blanks (no sales). As I showed last week, this is relatively easy with a calculated column. Here it is again.  Remember writing calculated columns first is a great way to visualise the problem you want to solve.  It is not a great way to solve most problems (some yes, most, no).

Read on to see how you can solve the problem using DAX Studio.

Comments closed

Understanding DAX’s LOOKUPVALUE Function

Alberto Ferrari explains how the LOOKUPVALUE works:

LOOKUPVALUE requires a column to retrieve a set of column/value pairs to provide the search conditions, and an optional default value in case there are either no matching rows, or too many matching rows. The following formula retrieves the exchange rate from the Daily Exchange Rate table, where Currency[Currency Code] matches EUR and ‘Daily Exchange Rate'[Date] matches Sales[Order Date]. In case there are no matches, it returns zero:

Alberto also provides a primer on the function in case you are unfamiliar with it, as this post starts with the assumption that you know what it does.

Comments closed

X Functions in DAX

Matt Allington explains what that “X” means in functions like MEDIANX and also builds out a problem to solve:

An X function is a class of functions in DAX that are also known as “iterators” (note, there are other iterating functions too, like FILTER, but I am only referring to the X functions here). It is a big topic on its own, and this article is not going to be the definitive guide to X functions. But I will give you a couple of insights.

I have learnt a lot about how to teach people DAX over the last 6 years, and my teaching methods have evolved over that time. I remember fondly speaking at the Microsoft Data Insights Summit with Will Thompson on the topic “DAX 50 – DAX for the rest of us“. Will said to me “don’t mention the word ‘iterator’ as it is too confusing.”. I didn’t agree with Will at the time, but his comment stuck with me. Over time I have changed the day way I teach DAX. These days I show people how to add a calculated column in a table (everyone can do that – its dead easy). Then I explain that an X function does exactly the same thing, it’s just that you can’t see the interim results materialised in front of their eyes.

Click through to see what Matt means and stay tuned for the next episode of the X Functions, where Matt reveals that there are aliens by use of bi-directional relationships.

Comments closed

Marking a Table in DAX as a Date Table

Alberto Ferrari explains why you have to mark a date table as such in Power BI:

Even though this probably does not surprise you, in fact it should. The measure – as it is written – should not work. The reason why it works is because the relationship between Sales and Date is using the Sales[Order Date] column. Sales[Order Date] has a Date data type. If we use an integer to relate the two tables instead of using a Date column, the formula stops working. We now change the relationship using the integer column Sales[OrderDateKey] instead of the Sales[Order Date].

Read the whole thing.

Comments closed

Getting Details by the Max Value of a Column in Power BI

Ed Hansberry solves a problem in a world without window functions:

When you group data in Power Query, you are able to do a number of aggregations, like count, sum, min, max, etc. The problem is each of those aggregations only operate on that column. Let me show you what I mean. Suppose you have this data:

You need to return a table to the DAX model (or an Excel table) that only has one record per product, and you want the latest date, and the sales person associated with that date. So for product A, it is June 18, 2020 and Suresh, for B it is June 14, 2020 and Ana, and so on.

In the SQL world, this is where a combination of common table expression and ROW_NUMBER() could get the top record partitioned by product ordered by date descending. Click through for Ed’s DAX-based solutions.

Comments closed

Understanding LOOKUPVALUE in DAX

Alberto Ferrari explains the LOOKUPVALUE function:

LOOKUPVALUE is one of the most widely used functions, especially for DAX developers who come from an Excel background. Indeed, the behavior of LOOKUPVALUE is very close to the behavior of the widely-adopted VLOOKUP function in Excel. Yet, there are important differences between the two; quite often, newbies use LOOKUPVALUE instead of creating a relationship between tables that ensures higher flexibility and higher performance.

This article is an introduction to LOOKUPVALUE. If you are interested in studying the internals of LOOKUPVALUE in more detail, make sure to check the advanced LOOKUPVALUE article; in that article, we describe the behavior of the function in detail along with several performance considerations.

Click through for the article.

Comments closed