Press "Enter" to skip to content

Category: DAX

Understanding LOOKUPVALUE in DAX

Published 2020-07-08 by Kevin Feasel

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

Another Way to Calculate Elapsed Business Hours with DAX

Published 2020-06-30 by Kevin Feasel

Matt Allington follows up on a previous post:

Then, sometimes (like this time) I discover that someone has a better way to solve the same problem that I shared on my blog. This is what happened last week after I shared my first article about how to calculate the total business hours between 2 date/time stamps. I shared the way I solved this problem last week, but one of my readers, Daniil Bogomazov, shared a brilliant alternative solution to the same problem. The solution is so good that I am sharing his solution with you here today.

Read on for a clever solution and a detailed comparison to Matt’s prior answer.

Comments closed

Role-Playing Dimensions in Power BI

Published 2020-06-29 by Kevin Feasel

Martin Schoombee explains the concept of role-playing dimensions and then explains how that works in the Power Bi world:

In technical terms a role-playing dimension is when a dimension table has multiple (foreign key) relationships to the same fact table. In more non-technical terms, it is when you have the same attribute (“Date” for instance) that can relate to the same metrics in different ways.

If you look at the data model below as an example, you can see the Date entity could be used to reference either the Invoice Date or Delivery Date from the Sales entity, changing the perspective of the metrics we’re looking at.

It’s a little surprising to me that there isn’t an easier way to handle this concept. Role-playing dimensions are a core part of the Kimball model, and they’re common enough that you’d expect support to be a bit simpler.

Comments closed

More Fun with 1-Column Fusion in DAX

Published 2020-06-26 by Kevin Feasel

Phil Seamark continues a discussion on single-column fusion:

You may notice the [Package – Bag] measure uses the * (multiplication) operator in line 4 between a measure and what looks like a column filter.

The [base measure] performs a simple COUNTROWS aggregation and returns an INTEGER. This side of the equation makes sense, so let’s see what is going on with the DAX on the right-hand side of the * operator? The filter statement is encompassed with parenthesis, which automatically converts the expression to a TRUE/FALSE boolean value – which is then implicitly converted to an INTEGER value of either 0 or 1. It can’t be anything else.

The result of this double conversion is that we end up with a INTEGER * INTEGER to produce the number we see in the visual.

Click through for plenty more where that came from.

Comments closed

Calculating Business Hours with DAX

Published 2020-06-25 by Kevin Feasel

Matt Allington combines DAX and a calendar table to calculate business hours:

I was helping a client this past week to calculate the total business hours between a start date/time and an end date/time, taking into account the working days, public holidays and non-working weekends, etc.  As is often the case, I thought it would be a great blog article that I could share with my readers.  This is a pretty involved problem with lots of moving parts, so as such I have decided to record a video showing you how I solved the problem, 1 step at a time.

Click through for the video as well as a description and the code.

Comments closed

Dynamic Measures in DAX

Published 2020-06-17 by Kevin Feasel

Kristyna Hughes explains the concept of dynamic measures in DAX:

For example, you may have three major stakeholders looking at a report: CEO, CFO, and COO. The CEO may be more interested in future profits and looks at trends in the number of loads booked to see where we are headed. The CFO wants to see profit trends on loads that have already been completed (actualized profit). Additionally, the COO would rather see what’s currently in transit and look at only picked up loads. You could make three pages, one based on booked loads for the CEO, another based on delivered loads for the CFO, and one based on picked up loads for the COO. But what happens if your report is already going to be multiple pages and needs room to grow? You would need to triple your page count to accommodate the needs of your users or build three completely separate reports! Don’t worry, dynamic measures can solve this problem for you without clogging up your reports or workspaces with extra measures, visuals, and reports.

Click through for the demonstration on how to combine this with a slicer to change reported measures.

Comments closed

One-Column Fusion with DAX

Published 2020-06-12 by Kevin Feasel

Phil Seamark has a performance tuning tip for DAX:

I wrote an article about an optimisation called DAX Fusion that attempts to fuse similar SE calls when it can. This article highlights an elegant DAX-based trick that works for a specific scenario by reducing the number of SE calls that doesn’t rely on DAX fusion. The difference between 1-Column fusion and the other is:

– DAX fusion in the engine works across multiple columns that have the same effective WHERE clause
– 1-Column fusion works by fusing multiple measures that all reference a single column, but with different WHERE clauses

Read on to learn how to switch around a bit of DAX to reduce the number of storage engine calls, as well as an example of one scenario in which it can come in handy.

Comments closed

Returning Multiple Values in Power BI with ConcatenateX

Published 2020-06-10 by Kevin Feasel

Nick Edwards shows how you can use the ConcatenateX DAX function to combine values:

In this blog post we’ll take a quick look at using ConcatenateX function to view a concatenated string of dates where the max daily sales occurred for a given month.

I came across this function whilst going through the excellent “Mastering DAX 2nd Edition Video Course” by the guys from SQLBI.com. So credit to Marco and Alberto for sharing this.

So how does it work? If we had a list of dates ranging from 01/01/2020 to 31/12/2020 and we wanted to see which days we achieved maximum sales for each given month in a year we could use the ConcatenateX function to return these dates in a single row per month.

Click through for the demo.

Comments closed

Expression-Based Formatting in DAX

Published 2020-05-18 by Kevin Feasel

Matt Allington points out a new look to a slightly less new feature:

When I say “new”, they are actually not new – this feature has actually been around since the start of 2019. What is “new” is the discoverability of the feature. Prior to the April 2020 release, you had to first hover your mouse button over the section (Title text in the example above). After you hovered your mouse, you would see a vertical … menu (kind of like a vertical ellipsis), then when you hovered over the vertical ellipsis, you would then see the Fx button. Click the mysterious hidden button and only then could you discover the world of expression based formatting.

Click through to learn about expression-based formatting and where it might be useful.

Comments closed

Getting a Substring with DAX

Published 2020-04-07 by Kevin Feasel

Reza Rad shows us how to build out a substring using DAX:

Substring is one of the most common functions in many languages, However, there is no function named Substring DAX. There is a very simple way of doing it, which I am going to explain in this post. Substring means getting part of a string, for example from “Reza Rad”, if I want to get the start starting from index 2, for 4 characters, it should return “za R”. Considering that the first character is index 0. Let’s see how this is possible.

The answer’s not as pretty as a SUBSTRING() function would be, but it’s also not too far off.

Comments closed