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

[youtube https://www.youtube.com/watch?v=2K4pYGRrMRg&w=784 &h=441]