Execution of a DAX query by the AI DAX engine in a DBMS using PostgreSQL as an example

Previously, a DAX query for SUMMARIZECOLUMNS was considered, and it still looks relevant now. Let's look at part of the diagram from dax.do with the fact table Sales and the customer table Customer.

There is also a DAX query that, as previously considered, is quite successfully converted to SQL for PostgreSQL:

EVALUATE
SUMMARIZECOLUMNS (
    Customer[Cars Owned],
    FILTER ( Sales, Sales[Quantity] > AVERAGE ( Sales[Quantity] ) ),
    FILTER ( Customer, Customer[Cars Owned] > 1 ),
    "Calculated Quantity",
        CALCULATE (
            SUMX ( Sales, Sales[Quantity] * RELATED ( Customer[Cars Owned] ) ),
            REMOVEFILTERS ( Sales[Quantity] ),
            FILTER ( Customer, Customer[Cars Owned] < 4 )
        )
)

The conditional "DAX AI engine" generates PostgreSQL for this DAX in fractions of a second through the OpenAI API, thanks to a simple query.

SELECT "Cars Owned", SUM(Quantity * "Cars Owned") AS "Calculated Quantity"
FROM sales
WHERE Quantity > (SELECT AVG(Quantity) FROM sales)
    AND "Cars Owned" > 1
    AND "Cars Owned" < 4
GROUP BY "Cars Owned"

It looks good, as previously considered, now it remains to execute this SQL on PostgreSQL, and then it will be the conditional "AI DAX engine for PostgreSQL without Power BI".

So far, this functionality exists only as a prototype on the portfolio site, however, the results can look quite interesting.

It is clear that the result of the "DAX AI engine" is displayed — SQL for PostgreSQL, corresponding to the original DAX, as well as a table with the results of the query execution.

As before, a denormalized Sales table was used to simplify the work of AI, but now the number of test records in Sales is not 50 million, but only 500 — to simplify development, only 2-3 fields from the tables were added, and not all fields of the Customer and Sales tables from dax.do:

CREATE TABLE sales
(
    "Order Number" INTEGER,
    CustomerKey    INTEGER,
    "Cars Owned"   INTEGER,
    Quantity       INTEGER
);

INSERT INTO sales("Order Number", CustomerKey, ""Cars Owned"", Quantity)
SELECT number + 100000000 AS "Order Number",
       number % 20        AS CustomerKey,
       number % 20 % 5    AS "Cars Owned",
       number % 10        AS Quantity
FROM generate_series(1, 500) as number;

Within the described data schema with two tables Sales and Customer, the DAX query can be "conditionally arbitrary", it all depends on the capabilities of AI. For example, if you add an expression like 1 + 2 * CALCULATE ( ... ) to the original DAX, you can notice the creativity of AI in the generated SQL and the use of CTE.

EVALUATE
SUMMARIZECOLUMNS (
    Customer[Cars Owned],
    FILTER ( Sales, Sales[Quantity] > AVERAGE ( Sales[Quantity] ) ),
    FILTER ( Customer, Customer[Cars Owned] > 1 ),
    "Calculated Quantity",
        1 + 2 * CALCULATE (
            SUMX ( Sales, Sales[Quantity] * RELATED ( Customer[Cars Owned] ) ),
            REMOVEFILTERS ( Sales[Quantity] ),
            FILTER ( Customer, Customer[Cars Owned] < 4 )
        )
)

WITH filtered_sales AS (
    SELECT "Cars Owned", Quantity
    FROM sales
    WHERE Quantity > (
        SELECT AVG(Quantity) FROM sales
    ) 
)
SELECT "Cars Owned", 
    SUM(Quantity * "Cars Owned") AS "Calculated Quantity", 
    1 + 2 * SUM(Quantity * "Cars Owned") AS "Final Quantity"
FROM filtered_sales
WHERE "Cars Owned" > 1 AND "Cars Owned" < 4
GROUP BY "Cars Owned"

Or in full, the entered DAX, the results of the AI DAX engine, and the results of the PostgreSQL query for the modified DAX with the expression look as follows.

I hope the results may be of interest when creating dashboards and working with Business Intelligence. I wish you success in BI and dashboards :)