It seems like everyone is playing with ChatGPT at the moment and doing everything from getting it to write code for them to asking it to write ode’s to their favourite visuals. Writing DAX code is interesting from the perspective of someone building a data model. But one thing that has always been hard from the perspective of consumer of a data model is understanding the logic behind those DAX calculations.
As you’ve probably already guessed from the title of this post rather than generating DAX I wanted to see if we could go the other way and get ChatGPT to provide simple descriptions of what a DAX expression was doing.
To test this I did not want to use a basic measure, so I found a moderately complex expression that someone else had actually originally had ChatGPT generate for them and ask it to explain the calculation. The first time I tried this it produced a very long description breaking down each of the function calls. But by revising the way I asked the question I eventually got to a point where it was giving me a nice concise explanation.
When I posted the following question to ChatGPT:
Explain the following calculation in a few sentences in simple business terms without using DAX function names: CALCULATE ( SELECTEDVALUE ( 'Suppliers'[CompanyName] ), TOPN ( 1, SUMMARIZE ( Products, 'Suppliers' [CompanyName], "@StockValue", SUMX( 'Products'[UnitsInStock] * 'Products'[UnitPrice] ), [@stockValue], DESC ) )
It came back with the following answer which I was pretty happy with:
This calculation is finding the company name of the supplier with the highest total value of stock for a given product. It does this by first multiplying the units in stock by the unit price for each product, then summarizing that total value by supplier. It then finds the top supplier by that total value and returns their company name.
This was great for a single measure, but not all of the consumers of your data model will have access to view the DAX expression. And even if they did it would be a pain to have to keep jumping out to an external website to understand what every measure is doing.
So this made me wonder if there might be some way of injecting these descriptions into the data model. And as it turns out ChatGPT already has a REST API. All you need to do is to sign up for an account at https://platform.openai.com (if you’ve been experimenting with ChatGPT you probably already have an account) and then generate an API key for your account and you can make requests of ChatGPT from another program.
Note: free accounts are limited to 20 calls per minute (see Rate Limits – OpenAI API ). So for large models you would either need to add logic to include a 1 minute delay every 20 measures or upgrade to a paid plan.
From there I setup the following Tabular Editor script which will loop through all of the measures in your data model and update the description with the one generated by ChatGPT. All you need to do to run this is to paste your own API key into the apiKey constant on line 8.
For illustration purposes I’ve also included the original DAX expression, but you don’t need to keep that if you don’t want to.
Note: the script has been tested in both TE2 (free) and TE3 (paid)
and is available as a gist on github https://gist.github.com/dgosbell/f3253c7ec52efe441b80596ffddea07c
The updated script including logic for dealing with rate limiting is included in my following post here
Before running the script hovering over a measure in my data model only shows the measure name
After running the script, you get a much more informative tooltip
Interestingly the descriptions do seem to change a little bit from one run to the next and in spite of asking it not to use DAX function names in the description sometimes it still sneaks one in. The description below was particularly bad where it just described the SUMX function.
But the advantage of this technique is that you can re-run it for single measures if you like or you could manually edit the description field if the description needed a small improvement.
Update: 16 Feb 2023 – added note about API rate limiting
Update: 17 Feb 2023 – See part-2 here for short tutorials on how to run the script and an updated version which deals with the API rate limiting
February 15, 2023 at 5:56 pm
That’s really cool, but where is the “Tabular Editor script”?
February 16, 2023 at 8:15 am
Sorry, it appears the plugin that was displaying the gist does not work properly all the time. I’ve added a direct link to the post now
February 15, 2023 at 6:01 pm
It’s amazing Darren, can you share the script?
February 16, 2023 at 7:47 am
Sorry the plugin for displaying the gist seems to not work all the time. I’ve added a direct link to it now.
February 15, 2023 at 11:19 pm
Did you forget the tabular editor script?
I don’t see it in the article when looking at it on my phone.
February 15, 2023 at 11:19 pm
Never mind the gist just slowed up.
February 16, 2023 at 7:48 am
Yes, the plugin that is showing the gist seems to have some issues. I’ve added a direct link to the gist in the post now as a backup.
February 16, 2023 at 1:09 pm
I just used it AMAZING!!!!
February 16, 2023 at 4:12 pm
It’s always giving me error 420, too many requests. Anything I can do in the code to work around this error?
February 16, 2023 at 4:12 pm
error 429 i mean
February 16, 2023 at 4:54 pm
A 429 error means you have hit the maximum rate limit for the API. I just checked and Free users are limited to 20 requests per minute. (see https://platform.openai.com/docs/guides/rate-limits/overview) so you could either add logic to pause for 1 minute for every 20 requests or you could upgrade to a paid plan.
February 16, 2023 at 9:31 pm
Oh my gosh. I was wondering if this was possible and here it is! Thank you!!
February 17, 2023 at 2:10 am
You sir are a genius! This is INSANE! Will save so much time writing descriptions for enterprise models. I can’t believe it even interprets the meaning of column names.
February 17, 2023 at 2:32 am
Hey Darren, this looks good!!
I’m a new Tabular Editor user, can you explain how I can run your script from there? Sorry for the dumb question.
February 17, 2023 at 2:37 am
Haha, sorry, you can ignore my question… I just ask it to chatGPT, lol.
February 17, 2023 at 8:01 am
lol – well done!
February 17, 2023 at 6:32 pm
Thanks for sharing these amazing techniques, but I getting “429 error too many requests” even I have tested on model with only 5 measures in it using both scripts?
Appreciate your help on this!
February 17, 2023 at 7:01 pm
If you are using the upgraded script from my second post which includes the pause in between batches of calls I’m not sure what else to suggest as I don’t see this issue on my machine. You could maybe try generating a new API key in case your current one has been flagged for too many requests. Otherwise as this error is coming from OpenAI so maybe try contacting them.
February 17, 2023 at 6:51 pm
This is really useful.
Thank you for sharing, Darren!
I tested the script on both large and small dataset.
Small datasets appear to be processed quite quickly, but even with an upgraded account, when I try to process a large dataset, I receive the 429 error.
February 17, 2023 at 6:58 pm
Have a look at my follow-up post https://darren.gosbell.com/2023/02/generating-measure-descriptions-with-chatgpt-part-2/ which includes an updated script which will add a pause inbetween batches. If you’ve upgraded your account you may want to change the apiLimit variable to match the upgraded limits.
February 24, 2023 at 3:20 am
Hi, Darren! Thanks for this tool! I’ve paid a monthly usage of Open AI API just for this, and works great, but:
1. sometimes I recive an error 500 in the line 32: “res.Result.EnsureSuccessStatusCode();” – it’s random, not every time I do it.
2. How could I choose which measures I’d like to run it? If I have +50 measures, and I only document 10-15 of them… how could I do it?
March 22, 2023 at 9:58 am
Error 500 is a server error. You’d need to ask the people at Open AI about that.
In terms of selecting specific measures you could use the script from my follow-up post here https://darren.gosbell.com/2023/02/generating-measure-descriptions-with-chatgpt-part-2/
March 26, 2023 at 8:47 pm
Just wanted to say “wow” and thanks for sharing – really impressive!
March 28, 2023 at 3:57 am
I am seeing an error
Error on line 32
Response Statue code does not indicate success :
March 28, 2023 at 1:20 pm
Is there more to that error message? Usually there should be an error code of some sort like a 429 if you have made too many requests or a 500 if there is a server side error.