AI Assistant for Tabular Editor - Power of Business Intelligence

For now, this is just a quick proof of concept, but it works (at least it worked on my side). It consists of two C# scripts: one script creates an assistant with an attached data model description (in JSON format), and the other script adds comments to a copy of the selected measure using the assistant.

I used Tabular Editor 3, but the script should work in Tabular Editor 2 as well. I believe so, although I haven’t used TE2 in a while.

Github: https://github.com/avatorl/TabularEditor/tree/main/ai-assistant

What is an assistant? When you create a custom GPT in ChatGPT, you can upload files and provide instructions that are stored permanently and used as a knowledge base for all future requests to that specific GPT. These files and instructions help tailor the GPT’s behavior and responses based on your needs.

An assistant API allows you to achieve a similar result via API—you can provide instructions, attach files, and even enable the assistant to call other APIs if necessary. The data provided to the assistant in this way remains accessible across interactions with the assistant.

For example, when you upload a Model.bim file (which contains a list of tables, columns, calculated groups and items, measures and measure expressions, and other data model metadata), the assistant will be able to analyze the file, and then add comments to the measures, and provide other forms of assistance, all while being aware of the entire data model. The assistant will retain this knowledge, enabling it to assist you with future queries related to this specific data model.

ai-create-assistant.csx

This script creates an assistant.

To start using the script, insert your OpenAI API KEY into the following line, replacing the OPEN_AI_API_KEY placeholder:

string apiKey = "OPEN_AI_API_KEY";

To create an API key read https://help.openai.com/en/articles/9186755-managing-your-work-in-the-api-platform-with-projects. You’ll need to create a project and then a new API KEY.

https://platform.openai.com/api-keys

I can’t export the data model as .BIM due to TE3 Desktop subscription limitations, but the script generates a simple JSON containing some details about the data model, including tables, columns, measures, and relationships.

If you wish to save the content as a JSON file for review, uncomment the following line:

// SaveDataModelToFile(jsonContent);

and update file path:

string filePath = @"D:\DataModel.json"; // Update the path as needed

Otherwise the JSON file will be uploaded into an Open AI storage without saving a local copy.

Run the script and it will create an Open AI assistant with DataModel.json attached.

List of assistants is available at https://platform.openai.com/assistants (you must be logged in to see it)

Once there is an assistant you can use the Playground to test it (to chat with it about the data model).

Playground chat with an assistant:

The list of uploaded files is available at https://platform.openai.com/storage/ (you must be logged in to see it).

The assistant ID will be placed into the clipboard. Use it to config the other script.

Current version of the scripts do not support any assistant management (there are no scripts to edit or delete an existing assistant). If data model has been significantly changed, you may need a new assistant to be created. Or use https://platform.openai.com/assistants/ to upload new DataModel.json manually, or to delete an assistant. Also, use https://platform.openai.com/storage/ to delete uploaded files from the storage.

ai-comment-measure.csx

This script interacts with the OpenAI assistant using the assistant ID. It sends a selected DAX measure to the assistant for commenting, and creates a new measure with the AI-generated DAX code.

To start using the script edit the following lines:

string apiKey = "OPEN_AI_API_KEY";
string assistantId = "ASSISTANT_ID"; // Replace with your Assistant ID

Note: ai-create-assistant.csx puts assistant ID into the clipboard

Now select a measure and run the script. The script should create a copy of the selected measure YourMeasure_Copy_with_AI_comments. The copy contains updated (with AI comments) DAX code.

Once an assistant created, you can use ai-comment-measure.csx script multiple times with the same data model. It’s OK to close Tabular Editor and .PBIX file and reopen them later. The assistant exists permanently (until deleted) on the Open AI server.

I do not trust AI enough to allow it to edit the existing measures. Therefore, the script just creates a copy of the measure so a human user can review it, edit it if required, and copy the code into the original measure.

This is just an experiment and a base for further development. Future versions will include scripts for managing existing assistant(s), optimizing the code, creating new measures, optimized prompts, and more.

Feel free to fork the repository and create a pull request if you made any useful changes.

Video: creating an assistant, using the assistant

Example

Original measure

Average Country Sales :=
AVERAGEX ( ALLSELECTED ( ‘Country’ ), [Sales Total] )

Measure copy with AI comments

Average Country Sales_Copy_with_AI_comments :=
— Calculate the average sales total for the selected countries
AVERAGEX (
    — Evaluate the expression for all selected countries
    ALLSELECTED ( ‘Country’ ),
    — Reference the sales total measure
    [Sales Total]
)

Security/privacy note: Do not publish code samples with your API KEY. Review the code and DataModel.json to see what the scripts sends to the Open AI server. In current versions it’s a list of tables, columns, measures, calculated columns and measures DAX expressions and other data model metadata. The script doesn’t send any data from the tables/columns.

2024-09-19 ai-create-assistant.csx has been updated to attach The Definitive Guide to DAX.pdf to the assistant

Example

Measure comments added by an assistant with DataModel.json only

— This measure calculates the Amount (AC) for the previous fiscal year
— It achieves this by creating a filter context for the previous fiscal year and applying it
— to the calculation of Amount (AC)
VAR _FY =
    — Retrieve the currently selected fiscal year
    SELECTEDVALUE ( ‘Date'[Fiscal Year] )
VAR _PrevFYFilter =
    — Create a filter for all dates where the fiscal year is the previous year
    FILTER (
        ALL ( ‘Date’ ),
        ‘Date'[Fiscal Year] = _FY – 1
    )
VAR _Result =
    — Calculate the Amount (AC) with the previous fiscal year filter applied
    CALCULATE (
        [Amount (AC)],
        _PrevFYFilter
    )
RETURN
    — Return the resulting value
    _Result

Measure comments added by an assistant with DataModel.json and The Definitive Guide to DAX.pdf

// This measure calculates the amount for the previous fiscal year based on the current context’s fiscal year.
// It retrieves the selected fiscal year and filters the Date table to get the corresponding period of the prior fiscal year.
VAR _FY =
    // Obtain the selected fiscal year from the ‘Date’ table
    SELECTEDVALUE ( ‘Date'[Fiscal Year] )
VAR _PrevFYFilter =
    // Create a filter for all records in the ‘Date’ table where the fiscal year is one year prior to the selected fiscal year
    FILTER (
        ALL ( ‘Date’ ),
        ‘Date'[Fiscal Year] = _FY – 1
    )
VAR _Result =
    // Calculate the Amount (AC) for the previous fiscal year using the _PrevFYFilter
    CALCULATE (
        [Amount (AC)],
        _PrevFYFilter
    )
RETURN
    // Return the result of the calculation
    _Result

Use your own copy of The Definitive Guide to DAX.pdf. Use it only to create your personal DAX assistant. Respect the authors.

Share the article