Power BI Multilingual made simple using user-context-aware calculated column

Hinzugefügt: 2026-05-31

[00:00:00]Do you want to build a multilingual Power BI semantic model and a report?

[00:00:07]Meaning [clears throat] that if someone from a French-speaking country look at your report from Power BI website, they see it in French language, category names, product names. If someone from English-speaking country see that, it would be in English. If someone from Spanish-speaking language country see that, it would be in Spanish.

[00:00:29]This is called translation, and we used to do translation in different ways in Power BI using external tools such as Tabular Editor. Now, you can do that using a newly announced feature called user context-aware [clears throat] DAX calculated columns in Power BI. My name is Reza Rad, and I'm going to show you in this video how you can do this.

[00:00:54]>> [music] >> We could create translation in Power BI or in semantic models in Analysis Services multi-dimensional. It was available through Visual Studio or what we used to call SS DT, which also was called as BIDS at some point.

[00:01:15]In tabular model, it was also available through Visual Studio, but then when Power BI existed and created Power BI Desktop didn't have that function to enable translation. So, we used external tools such as Tabular Editor to do that.

[00:01:30]You could also use Tim Del to drive somehow the way that you manage translation. But recently, and when I say recently, this is like this month, earlier this month, or even like late April better to say, we have a new function feature announced in Power BI called user context-aware calculated column. This is a calculated column that is aware of the user context, aware of the locale of the user that logins to the system, aware of the user information such as user principal name. Some of the functions that we have in DAX now work with this type of calculated column, which previously we couldn't get them in a calculated column. They've been only available in a measure. Now that we have them in a calculated column means it means that our values can be written by an expression in a way that they respond to a user context such as, for example, what is the locale or the language that the user logs in with or what is, for example, the username. So, you could do a lot of scenarios. You could do row level security in a different way. You could do translation. You could do multilingual report and semantic model.

[00:02:47]In this video, we are talking about multilingual semantic model. So, let's jump into my demos so that I can show you how this works. So, first, to get this working, you'll need one of the recent versions of Power BI Desktop.

[00:03:00]Once you get that, if the time that you are watching this video, this feature is still preview, it means that you have to go and enable it.

[00:03:09]I'm enabling my zooming tool.

[00:03:12]This is under files, under options and settings, options and in the previous sections here, we would have all the features that are recent, still in preview, meaning that they are beta edition. We can find them and enable them. For this specific feature we are talking about, it would be under preview features and it would be called this, user context aware calculated column. Now, what that is. Here, I have a Power BI semantic model, as you can see, with a simple model. This is three tables, fact table, customer table, product table. In the product table or customer table, let's go to the customer table for example. In the customer table, you can see that I have multiple columns for education. I have the education in English language, I have it in Spanish, and I also have it in French. Uh there should be like a French uh education here as well. So, here you can see all the three.

[00:04:16]Now, I have these as separate values in my semantic model, but what I want to do is that I want to build a report like this, a visual like this that has the education, that has the sum of sales amount, whatever metric that I want to show.

[00:04:31]And then whenever a user log in into the Power BI semantic model, if their browser setting is, for example, French, they see these values in French without me going and changing the column that is used here. So, how we can do that? We can create a calculated column, of course.

[00:04:49]We could create calculated column previously as well. This is nothing strange, but one of the things that is different this time is that when you create a calculated column, so when, for example, you go to the customer table, and then when you go to creating a new column, it is the still the same column, but once you create that column, and here I created one column just to show you, or let me just create it one from a scratch. So, I'm creating a new column in here.

[00:05:22]Let's just call it column one.

[00:05:26]And in this column one, I can write whatever expression. Like, for example, I can refer to a function or anything in here, but one of the functions that I cannot use, for example, is user culture. User culture uh or things such as user principal name, these are for functions that you cannot use inside the calculated column. And if In fact, if you try to use it, you will get an error saying that this type of this type of functions is not allowed in a calculated column. Here you can see custom data, username, user principal name. These functions are only possible to be created inside a measure. And why is that? Because measure calculation is dynamic. It is not precalculated. It happens on the fly. And user culture changes when the user log into the system. This is not at the time that we refresh the Power BI semantic model.

[00:06:23]This is the calculated column general calculated column. This new type of calculated column, which here I can go to the advanced tab of the calculated column, and I can change this expression context. So, inside the advanced tab, if I change the expression context instead of standard, which is the default one, which is the one that we all used to so far, now I change it to user context. This means that this calculated column will now be aware of user context. But it also means that the calculation is done differently. The calculation is not precalculated anymore. It is somehow calculated similar to the measure on the fly, but it is a still row-based. So, something between calculated column and a measure.

[00:07:08]User context calculation. And now that I have this, if I try to check this one more time, I hit the check mark, and you see that I don't get any errors. This time it works perfectly fine. And that is exactly what this user context calculated column does. It takes into account what is the user logging in.

[00:07:32]Calculation is somehow similar to measure, happens on the fly, so you have to be careful about the performance.

[00:07:39]Don't have heavy transfer heavy calculations in this expression, but it is useful for a lot of things. What is this useful for example? Here I have a measure exactly with the same thing user culture. This is a measure. I added it as a measure because then I can have it in a card visual like this. Here you can see I have that measure in a card visual which shows me what is the culture setting in my local computer. e n US Now, what I have done is that I went and created a calculated column. This is the education calculated column. As you can see this expression context is user context. So, it is not a standard. It is not calculated at the time of refreshing the report. It is based on the user data. What I have done here is that I have used this function user culture.

[00:08:30]I wrote that into a variable. You could use variable or you could just use user culture directly in here. I just used it as a variable because I like to have it like separately years of uh development.

[00:08:41]So, this culture variable now has what is the user culture which in this case would be e n US.

[00:08:48]Then based on that value, I would use a switch a statement. Switch a statement is like a if statement. I'm saying that if then I just get the left two characters of user culture. For example, e n I don't care if it is e n US, e n UK, e n n z, e n a u. In all of these cases it is e n and I want to show you English unless I have different English columns for each of these countries. So, what I'm going to do is I'm just getting the left two characters of this which would be e n. Then I'm saying that if it is e n, switch works like this. If this value is equal to e n then give me education English.

[00:09:30]If it is e s Spanish.

[00:09:33]If it is f r French. You could go and add as many as these as you want depending on how many columns you have.

[00:09:40]And then the last statement of switch without any value to match is what happens if it is none of these, if it is else, right?

[00:09:49]So, this is a really simple switch statement. You can find more about switch expression in a blog and a video that I have specifically about this.

[00:09:58]Now, what happens is that when I go to my report view, because this is a calculated column, I could easily go and use that calculated column as slice and dice field in a table. I could even go and create this as a bar chart if I want to. Because it is not a measure, it is a calculated column, so I can go and use this over there as well. So, this education value that I have here is now coming from that calculated column which is user context aware. Then if I save this report and I'm going to save this and publish it into the Power BI website that I have and I'm publishing it into a non-fabric semantic a non-fabric workspace. You don't need to have fabric capacity to use this function. It just works as simple as that. So, when I publish it over there inside my Power BI website, this is taking a minute to publish. Inside my Power BI website, this is the previous version of that report that I had. If I refresh it and hopefully we have the new version appearing here soon. I should see that.

[00:11:07]So, here you can see that all of that is in English because the culture here is English.

[00:11:15]Now, if someone goes and open the same report if they have access to the same report and this time they open it from a browser from a computer that has French settings, they will see it in French.

[00:11:27]Now, one way for me to show it is actually copying this into a new browser tab and then adding something like language is equal to FR, which would show me that in French. [snorts] That would of course change these settings into French. As you can see, the dates is in French. These values are in French. But what you can see, which is the most important part that we have done, we have achieved, is this. This is picking the values from the French education column. If I do the same thing this time with language of Spanish, I would see this configuration this time with Spanish language. So, quite quite useful. Here you can see this as well. Quite useful. The same report provide three different outputs for three different user culture. This is the user culture function that I used. You could use custom data. You could use user principal name. There are so many scenarios, interesting scenarios that you could use this user context aware calculated columns. But be very careful.

[00:12:36]Don't have heavy calculations on these because these are calculated on the fly.

[00:12:41]But they are fantastic. I would highly recommend you to go and use them. They change the way that we do translation in Power BI and many other things. I hope this video helped you to understand more about how this feature works in Power BI. We have videos weekly about Power BI, Microsoft Fabric, and AI.

[00:13:00]Uh if you like this video, go ahead and subscribe into our YouTube channel and watch our weekly videos on these subjects. Until the next video. Bye.

[00:13:13]>> [music]