Translate your measures using Tabular Editor and GPT3

There is a lot of hype these days about Large Language Models.
While they are excellent for some tasks, they still can’t replace a good BI developer.

However – they can be used for a lot of helpful things, so heavily inspired by the great articles written by Darren Gosbell:
Automatically generating measure descriptions… & Generating Measure descriptions with ChatGPT – part 2
and the follow up by Erik Svensen: Document your #powerbi model with #chatGPT…
I wanted to add my own flavor of how to use the new cool AI features to build better data models.

So here is my attempt to help you quickly translate your model to multiple languages. Initially just for the measures (and only the names of those, not descriptions and display folders), but the same approach could be used for all texts that can be translated in the model.

Preparing your model

To begin with, you should open up your model with Tabular Editor and create the desired languages under Translations

Next, you should open a new C# script and copy/paste this block of code in there:

#r "System.Net.Http"
using System.Net.Http;
using System.Text;
using Newtonsoft.Json.Linq;

// You need to signin to https://platform.openai.com/ and create an API key for your profile then paste that key 
// into the apiKey constant below
const string apiKey = "<YOUR API KEY HERE>";
const string uri = "https://api.openai.com/v1/completions";
const string question = "In the context of an analytical data model, could you please translate the measure name \"{0}\" in the table \"{1}\" from English (en-US) to {2} with short precise business terms?\n\n";

const int oneMinute = 60000; // the number of milliseconds in a minute
const int apiLimit = 20;     // a free account is limited to 20 calls per minute, change this if you have a paid account
bool dontOverwrite = true; // this prevents existing descriptions from being overwritten

using (var client = new HttpClient()) {
    client.DefaultRequestHeaders.Clear();
    client.DefaultRequestHeaders.Add("Authorization", "Bearer " + apiKey);

    int callCount = 0;
    
    // if any measures are currently selected add those to our collection
    List<Measure> myMeasures = new List<Measure>();
    myMeasures.AddRange( Selected.Measures );

    // if no measures were selected grab all of the measures in the model
    if ( myMeasures.Count == 0) {
       myMeasures.AddRange(Model.Tables.Where(t => t.Measures.Count() > 0).SelectMany(t => t.Measures));
    } else {
		dontOverwrite = false;
	}
        
    foreach ( var m in myMeasures) {

		foreach (var culture in Model.Cultures) {
			var currentCultureName = culture.Name;
			var currentCultureDisplayName = culture.DisplayName;

			// if we are not overwriting existing descriptions then skip to the next measure if this one is not an empty string
			if (dontOverwrite   !string.IsNullOrEmpty(m.TranslatedNames[currentCultureName])) {continue; }
			
			// Only uncomment the following when running from the command line or the script will show a popup after each measure
			//Info("Processing " + m.DaxObjectFullName);

			var body = 
			"{ \"prompt\": " + JsonConvert.SerializeObject(String.Format(question, m.Name, m.Table.Name, currentCultureName)) + 
				",\"model\": \"text-davinci-003\" " +
				",\"temperature\": 1 " +
				",\"max_tokens\": 2048 " +
				",\"stop\": \".\" }";

			var res = client.PostAsync(uri, new StringContent(body, Encoding.UTF8,"application/json"));
			res.Result.EnsureSuccessStatusCode();
			var result = res.Result.Content.ReadAsStringAsync().Result;
			var obj = JObject.Parse(result);
			var desc = obj["choices"][0]["text"].ToString().Trim();
			m.TranslatedNames[currentCultureName] = desc.Trim('"');
			
			callCount++; // increment the call count
			if ( callCount % apiLimit == 0) System.Threading.Thread.Sleep( oneMinute );
		}
    }
}

If you haven’t already done so when playing with Darrens example, you now need to create your own API key and replace in the script.
See his blog for more info on how to do that.
Now I suggest saving as a macro for easy reuse with any model you open – you can also just run it from the C# script editor.

Now all you need to do, is to right click your measure(s) and click AI -> Translations -> Translate measure using GPT3:

Thank you to Darren & Erik for sharing their thoughts. I hope you like this expansion to their great work.