For this tutorial, I’ll be showing you different ways of how to combine values using advanced DAX functions. This is an amazing DAX formula and I’ll be teaching you three different ways of how to use it. You may watch the full video of this tutorial at the bottom of this blog.
The COMBINEVALUES function is not a difficult DAX function to understand. It’s very simple and is similar to when you concatenate your data.
In Power BI, the CONCATENATE function is used to join two text strings into one. So, if you’ve done any of that work, you’ll know exactly what I mean. You can just use a simple ampersand (&) to join two certain text values. But the COMBINEVALUES function is a better way to write it.
I’ll work through a few examples here so you can see how you can integrate it into your Power BI and DAX formulas. You can combine or concatenate values using DAX in a number of ways. For this tutorial I’ve done it in three ways – I used COMBINEVALUES in a calculated column, a calculator table, and I’ve also used it in a measure table.
Combine Values in Columns Inside Date Table
In this first example, I’m trying to join two particular columns inside my date table.
But I made sure to put a comma because I want to make it customized.
Here, I used the COMBINEVALUES function and placed it in the right delimiter in whatever year or month that I want. You can also use other values – it’s pretty simple!
Using COMBINEVALUES Within Calculator Tables
Another thing that you can do to concatenate or combine values using DAX is to utilize it within calculator tables.
Firstly, click New Table from the Modeling tab of Power BI.
Now, I wanted to create a brand new table with a specific and unique column and information from my dates table. The way the Months column looks doesn’t actually exist in my dates table. Well, you can set this up in the Query Editor. But for this example, I haven’t set this up in my dates table. I just used the Months Years formula to come up with the data for the said column.
What I want to achieve using this formula is to get a list of all the unique months and years using the DISTINCT function.
Next, I want to bring in the index number because I want to sort this data correctly.
I know I already have the Index column in the table, but I just wanted to create a different one. The one that allows me to sort the months and year correctly.
I might also use this in my model. For instance, I might want to put it up as another lookup table or a different layer.
There’s some potential for this to be used; such as if you have some monthly budgeting information and you want to filter it using a table rather than using your dates table.
As I worked through these techniques, I realized that there are other ways you can do it. That’s the great thing with Power BI because there are multiple ways of doing things to get the same results.
Instead of using CONCATENATE, you can actually utilize the COMBINEVALUES function to simplify the logic that you’re writing. It’s just like the DIVIDE function. Instead of using the divide operation, you simply write the word divide.
Combine Values Using DAX Inside A Measure
The last example I want to go over is how to combine values using DAX inside a measure. I also want to show how you can create a dynamic text by utilizing the COMBINEVALUES function.
Check out what I’ve done here. I’ve placed the text “The first month and year in the current context is”. Then, I’ve used COMBINEVALUES to return two particular elements from my date table separately.
Meanwhile, this part of the formula below works out the minimum month and years.
I can’t just use MIN because it will go and find the first month alphabetically. Firstly, I need it to find the first short month. But to identify what the short month is, it needs to calculate the minimum month. Next, it’s going to filter the dates table for that first particular month. Thus, it’s always going to be January in that case if you have a year selection. Now, I can finally work out the minimum dates for the short year here from the dates table.
As you see, I basically just combined two particular columns, but now it’s just more dynamic. So when I select a different year inside the measure, the logic updates and combines the values.
***** Related Links *****
Using Iterating Functions SUMX And AVERAGEX In Power BI
Working With Iterating Functions In DAX
How To Use The CROSSJOIN Function
Conclusion
Although this is a short tutorial, you’ll see that the COMBINEVALUES function is helpful. In short, when it comes to your formulas, it’s all about making them appear more intuitive.
I’m always big on making things really simple and intuitive in all aspects of your work inside Power BI. This is just another good example of a nice clean function that enables you to bring information together with text values quite effectively.
If you enjoyed learning about this one, don’t forget to subscribe to Enterprise DNA TV. There will be lots of great content coming your way soon!
Sam
