Category Archives: Quick Tip

Turning multiple values into one string in Power BI

Or concatenating if you will, but I don’t always remember that word which is probably why the solution didn’t come to me at once when I ran into the problem.

Concatenation is taking two or more values and chaining them, often with a delimiter. Like the first and last name of a person, or a zip code and the name of the area.

This demo data set is made up by looking at a persons grades in different classes they have taken. So if we want to show that information for all persons we would typically show it in a table like this.

Now what if we didn’t care to much about the grades for now, and wanted to show which classes a person had taken. We could of course just remove the Grade column and keep the table, but what if we only want to have one row per person? My gut feeling was playing with the matrix in Power BI, but that only shows the first value since this is a text column so it’s not what I am after at all.

The answer? Concatenation in a measure. Very often concatenation is done between two columns, but here I need it done on rows so the CONCATENATEX function is what we want. First I made this measure

Classes non distinct = CONCATENATEX(Example,Example[Class],”, “)

which gave this result

Looks ok, but the total row has duplicates in it and if we had grades for several years we would have it on each person as well if they had taken the same class several times and I only want the distinct values. Luckily we can then use the VALUES function to only get distinct values in a column so the measure now looks like this.

Classes = CONCATENATEX(VALUES(Example[Class]),Example[Class],”, “)

Which gives this result

Perfect! This can now be filtered and used like any other measure.

Power BI Quick Tip – Moving Shape to Background

Ever used shapes or images in Power BI and wanted a more flexible way of moving things forward and backwards on your canvas? Yeah, me to!

Under “View” there is a pane called “Selection Pane” and when checked you will see all the different elements on our page, and you can arrange them as you like where the ones on top of the list will be in the front of the elements below on your page. So if you use a shape for a background as me below, just pull it all the way to the bottom, and hepp! It is correctly placed as a background, and not covering the important stuff.

Activate the “Selection Pane” to reorder visuals
Before: My shape is in front of a lot of my graphs
After: My shape is correctly move to the background