kohera-logo-regular.svg

There’s always an insight with Power BI: CONCATENATEX

Not so long ago, I was working with a client who had a specific Power BI request. They wanted to do a lookup of a column in another table, but if there were multiple hits, they wanted to display all the matching rows in the same cell (Power BI doesn’t really know the concept of cells, but you know what I mean). Let’s give you an example. Imagine we have a very simple Clients dimension table with only three clients:

And an Orders fact table with these clients’ orders (clients can, of course, have multiple orders):

The model is as simple as the tables:

Now, what the client wanted to achieve was this:

Why?

At first, I was somewhat hesitating to look for a solution. What’s wrong with multiple rows (one for each order date). And let’s say we put all order dates in one cell, how will it look when you try to squeeze 50 order dates in one cell!? Well, the business of the client was different, so from a visual point of view, a maximum of four values could show up in a cell, but usually no more than one or two. The second reason to solve this was that the technical me wanted to know how to achieve this.

It doesn’t always have to be difficult

When starting to search for a solution, I wasn’t sure I would actually find one. And then I found out that there is a CONCATENATEX function in Power BI (it’s also available in SSAS 2016 and Power Pivot in Excel 2016). It simply concatenates the result of an expression, and you can even choose your delimiter and sort order (but the sort order isn’t properly documented at the time of writing).

So, let’s take a look at the solution. This is the DAX formula for the calculated column in the Clients table:


Order Dates =

CONCATENATEX(

FILTER(Orders; Clients[ClientId] = Orders[ClientId]);

FORMAT(Orders[OrderDate]; "yyyy/mm/dd");

", ";             // the delimiter

Orders[OrderDate] // sort ascending by order date

)

So, we look for all the matching records in the Orders table and we concatenate the order dates of these records. When displayed in a report it looks like this:

Simple, but very helpful in certain cases.

 

 

Parameter sniffing solved with new Parameter Sensitive Plan Optimization feature

If you’re someone that works a lot with Microsoft SQL Server, there’s no doubt that you’ve had issues with an issue called “Parameter sniffing” before. Up until now, you had...

Creating maps with R and Power BI

The possibilities are infinite when it comes to creating custom visuals in Power BI. As long as you have creativity and knowledge about the right programming language, you can let...

Sending monitoring alerts through Telegram

What if you could get the ease of phone notifications for whatever monitoring alerts you need? Then we have a solution for you with the app Telegram. Some of you...

Send mails with Azure Elastic Database Jobs

The DatabaseMail feature in SQL Server and Managed Instance is widely used by many professionals. But what if you want a similar functionality in Azure SQL Database? There are options,...

Sorting matrices in Power BI

Recently I worked on a Power BI project for a client. They had a SharePoint site where they regularly published articles and wanted to pour view data into a report...

The world of data is evolving

The data landscape has changed dramatically over recent years. In the past, we mainly heard that we needed to do as much as possible “cloud-only”—but this trend has become more...