Skip to main content

Concatanate Like a Pro

When we need to concatenate two strings into one in Power BI, the first option that comes to mind is CONCATENATE or CONCATENATEX. While CONCATENATE works with columnar based structure, CONCATENATEX is an iterator function, needing a table to iterate row by row. But actually, we have a new player in the game since 2017 and it is called COMBINEVALUES. Hence it's a relatively new function, it's not used as much as the others, and that's why I have decided to write a post about it. First let's try to concatenate without using any function, only with the ampersand operator.

    
    And this is the output :
       
As you see it's pretty simple, there is no room for surprise. Before doing the same thing with CONCATENATE, let's check the syntax first:

CONCATENATE(<text1>, <text2>)

As you see, it accepts only two arguments. To concatenate multiple columns we can either write the function with the ampersand operator (&) again and again like this one : 

     Or we can create the same formula using nested functions :
     
All of them have the same output but look very different than each other. Especially the last one is unnecessarily long, also difficult to read and edit later. It's hard to believe you need such a long code to write something that simple. So let's check what does Microsoft says for our new player, which is introduced as COMBINEVALUES.

COMBINEVALUES(<delimiter>, <expression>, <expression>[, <expression>]…)

The first difference, the delimiter is written only once in the beginning, meaning you don't need to repeat it between every expression. I think it is a pretty good improvement. The second is you can concatenate an unlimited number of strings, which saves you writing the same function again and again for the same result. It means, it definitely improves the readability of your code. It can be used in measures as well as in calculated columns, like composite keys, etc. The thing that you should be aware of, it doesn't accept nothing as a delimiter, gives error on that occasion. If you need to concatenate without having a delimiter, this one is not going to work for you, using one of the other options that I mentioned is needed.  And here it comes: 

I think this one would win the competition of which code looks the most readable But when I used the performance analyzer to test which one is the fastest, the champion was CONCATENATE. And the moral of the story is: There are many ways to do the same thing in DAX. The answer to "Which one is the best ?" depends on the context and performance issues. Before I finish I would like to add that the equivalent of COMBINEVALUES in SQL is CONCAT_WS which is also introduced in 2017. Last but not least, in Excel, you can use CONCATENATE function to join multiple strings up to 255 arguments.

Labels:

Comments

Post a Comment

Popular posts from this blog

Manipulating Grand Totals with ISINSCOPE in Power BI

ISINSCOPE function was introduced in with November 2018 Power BI update. We can say it's a very young player in the game, but it's definitely magic. It can be used to manage lots of issues related to hierarchies, but in this post, I'm going to focus on manipulating the grand total line with it.  This is my budget forecast table. The budget forecast that you see for every month is not the forecast for every month, but it's the forecast for the whole year. The budget forecast for 2020 has changed every month. That's why we have different numbers for every row. Below you see the table visual with the year-month column and a measure that shows the values for every month. The thing that I want you to pay attention to is the total line. I wanted to see the last month's value for the total line and this is the measure for that.  This measure calculates this: If the year-month column has one value then take the average of that value. If it doesn't have one value, li...
Post a Comment
Read more

How to Add Flags to Power BI Reports

Adding flags to Power BI Reports might be easier said than done. If you have a dataset which contains data of various countries, adding country flags looks cool. The process to do it, on the other hand, might be compelling. Now I am going to share with you the obstacles I  have faced during the process and of course, how I overcame them. First I made an excel list of countries that my dataset has. Then I have downloaded to my computer all the flags I need from Wikipedia and converted them to Base64 link via an image encoder website  base64-image.de  and added those links to my excel file, which looks like this: After I added my excel file to Power BI, I converted the data category of URL column from 'uncategorized' to 'image URL', which might be a small step for you but it's a big step for the success of the flag operation. Then I created one to many relationship with DimLocation table and chose 'both' as filter direction, so that I can filter from both t...
Post a Comment
Read more

Solving Data Type Conflicts in Power BI and SQL

Whether we like it or not, error messages are part of our lives. The other day, I faced one of those little horrid yellow messages. For me, it's impossible to stay chill when I see that message. For a second, I feel alarmed and panicked. Let me show you what I'm talking about. The first column is the customer code, quite self-explanatory. The Registration Date column shows the date when the customer registered to my shop. The First Sale Date column shows the date when the customer bought something for the first time. And this is what I'm trying to see in the Sales Day column: If Registration Date and First Sale Date are the same, meaning customer bought something at the same day customer registered to my shop, print  "First Day", otherwise show the data of First Sale Date column. But computer says no! It says "Expressions that yield variant data-type cannot be used to define calculated columns." If I need to translate it in simplified English, it means: ...
Post a Comment
Read more