There are different ways we can build multiple measures at once in Power BI, and we can ask AI to build us the scripts to do the job for us. First,I will tell AI what tables I have from the publicly available Adventure Works database and then ask for generate some useful measures we can use.
The methods we use are as follows:
1. DAX query (as part of Power BI desktop).
2. Tabular Editor (third-party tool)
3. TMDL
DAX query multiple measure creation.
The first method is to create a script in DAX query:
The tables that I am using are below:
AI gives us the code we need to create 10 measures for the Reseller table.
It creates a nice DAX query that we can use to add measures.
-- Define 10 foundational measures for AdventureWorksDW Reseller model
DEFINE
MEASURE FactResellerSales[Sales] =
SUM ( FactResellerSales[SalesAmount] )
MEASURE FactResellerSales[Total Cost] =
SUM ( FactResellerSales[TotalProductCost] )
MEASURE FactResellerSales[Gross Profit] =
[Sales] - [Total Cost]
MEASURE FactResellerSales[Gross Margin %] =
DIVIDE ( [Gross Profit], [Sales] )
MEASURE FactResellerSales[Order Count] =
DISTINCTCOUNT ( FactResellerSales[SalesOrderNumber] )
MEASURE FactResellerSales[Units Sold] =
SUM ( FactResellerSales[OrderQuantity] )
MEASURE FactResellerSales[Avg Order Value] =
DIVIDE ( [Sales], [Order Count] )
MEASURE FactResellerSales[Sales YTD] =
TOTALYTD ( [Sales], 'DimDate'[FullDateAlternateKey] )
MEASURE FactResellerSales[Sales PY] =
CALCULATE ( [Sales], DATEADD ( 'DimDate'[FullDateAlternateKey], -1, YEAR ) )
MEASURE FactResellerSales[Sales YoY %] =
DIVIDE ( [Sales] - [Sales PY], [Sales PY] )
-- Example result set to validate measures (optional)
EVALUATE
SUMMARIZECOLUMNS(
'DimDate'[CalendarYear],
'DimProductCategory'[EnglishProductCategoryName],
'DimProductSubcategory'[EnglishProductSubcategoryName],
"Sales", [Sales],
"Gross Profit", [Gross Profit],
"GM %", [Gross Margin %],
"Orders", [Order Count],
"Units", [Units Sold],
"AOV", [Avg Order Value],
"Sales YTD", [Sales YTD],
"Sales PY", [Sales PY],
"YoY %", [Sales YoY %]
)
ORDER BY 'DimDate'[CalendarYear], 'DimProductCategory'[EnglishProductCategoryName], 'DimProductSubcategory'[EnglishProductSubcategoryName]
I can then add them to my model by clicking on each measure: ‘Update model: Add measure’. So it does take a little effort.
My measures are created, but disorganised, so I’d like them in a folder without us having to do the work. This is where the tabular editor comes in.
Tabular editor C# Script
We can use AI to give us a C# script that we can run in the Tabular editor (you need to enable unsupported features in File > Preferences), for it to run, but it does run. Note I’m using the free tabular editor 2 here.
// AdventureWorksDW 2022 - Starter measures (create or update)
// Target table: FactResellerSales
// Folders: Measures\Sales Performance, \Customer Insights, \Product Performance, \Time Intelligence
var t = Model.Tables["FactResellerSales"];
if (t == null) throw new Exception("Table 'FactResellerSales' not found.");
// create-or-update helper
int created = 0, updated = 0;
System.Action<string,string,string,string> Make = (name, expr, folder, fmt) =>
{
Measure m = null;
foreach (var mm in t.Measures) { if (mm.Name == name) { m = mm; break; } }
if (m == null) { m = t.AddMeasure(name, expr); created++; } else { m.Expression = expr; updated++; }
if (!string.IsNullOrWhiteSpace(folder)) m.DisplayFolder = folder;
if (!string.IsNullOrWhiteSpace(fmt)) m.FormatString = fmt;
};
// folder constants
var F_Sales = "Measures\\Sales Performance";
var F_Cust = "Measures\\Customer Insights";
var F_Prod = "Measures\\Product Performance";
var F_Time = "Measures\\Time Intelligence";
// SALES PERFORMANCE
Make("Sales", "SUM(FactResellerSales[SalesAmount])", F_Sales, "#,0");
Make("Total Cost", "SUM(FactResellerSales[TotalProductCost])", F_Sales, "#,0");
Make("Gross Profit", "[Sales] - [Total Cost]", F_Sales, "#,0");
Make("Gross Margin %", "DIVIDE([Gross Profit],[Sales])", F_Sales, "0.00%");
Make("Order Count", "DISTINCTCOUNT(FactResellerSales[SalesOrderNumber])", F_Sales, "#,0");
Make("Units Sold", "SUM(FactResellerSales[OrderQuantity])", F_Sales, "#,0");
Make("Avg Order Value", "DIVIDE([Sales],[Order Count])", F_Sales, "#,0.00");
Make("Avg Unit Price", "DIVIDE([Sales],[Units Sold])", F_Sales, "#,0.00");
Make("Avg Unit Cost", "DIVIDE([Total Cost],[Units Sold])", F_Sales, "#,0.00");
Make("Profit per Unit", "DIVIDE([Gross Profit],[Units Sold])", F_Sales, "#,0.00");
Make("Profit per Order", "DIVIDE([Gross Profit],[Order Count])", F_Sales, "#,0.00");
// CUSTOMER INSIGHTS
Make("Distinct Customers", "DISTINCTCOUNT(FactResellerSales[CustomerKey])", F_Cust, "#,0");
Make("Sales per Customer", "DIVIDE([Sales],[Distinct Customers])", F_Cust, "#,0.00");
Make("Orders per Customer", "DIVIDE([Order Count],[Distinct Customers])", F_Cust, "#,0.00");
Make("Units per Customer", "DIVIDE([Units Sold],[Distinct Customers])", F_Cust, "#,0.00");
Make("Profit per Customer", "DIVIDE([Gross Profit],[Distinct Customers])", F_Cust, "#,0.00");
// PRODUCT PERFORMANCE
Make("Distinct Products", "DISTINCTCOUNT(FactResellerSales[ProductKey])", F_Prod, "#,0");
Make("Sales per Product", "DIVIDE([Sales],[Distinct Products])", F_Prod, "#,0.00");
Make("Profit per Product", "DIVIDE([Gross Profit],[Distinct Products])", F_Prod, "#,0.00");
Make("Sales Category Share","DIVIDE([Sales], CALCULATE([Sales], ALL('DimProductCategory')))", F_Prod, "0.00%");
Make("Profit Category Share","DIVIDE([Gross Profit], CALCULATE([Gross Profit], ALL('DimProductCategory')))", F_Prod, "0.00%");
// TIME INTELLIGENCE
// Make sure DimDate is marked as Date table and FullDateAlternateKey is a Date type
var dateCol = "'DimDate'[FullDateAlternateKey]";
Make("Sales YTD", "TOTALYTD([Sales], " + dateCol + ")", F_Time, "#,0");
Make("Profit YTD", "TOTALYTD([Gross Profit], " + dateCol + ")", F_Time, "#,0");
Make("Sales PY", "CALCULATE([Sales], DATEADD(" + dateCol + ", -1, YEAR))", F_Time, "#,0");
Make("Profit PY", "CALCULATE([Gross Profit], DATEADD(" + dateCol + ", -1, YEAR))", F_Time, "#,0");
Make("Sales YoY %", "DIVIDE([Sales]-[Sales PY],[Sales PY])", F_Time, "0.00%");
Make("Profit YoY %", "DIVIDE([Gross Profit]-[Profit PY],[Profit PY])", F_Time, "0.00%");
Make("Sales MTD", "TOTALMTD([Sales], " + dateCol + ")", F_Time, "#,0");
Make("Profit MTD", "TOTALMTD([Gross Profit], " + dateCol + ")", F_Time, "#,0");
Make("Sales QTD", "TOTALQTD([Sales], " + dateCol + ")", F_Time, "#,0");
Make("Profit QTD", "TOTALQTD([Gross Profit], " + dateCol + ")", F_Time, "#,0");
Make("Sales Rolling 12M", "CALCULATE([Sales], DATESINPERIOD(" + dateCol + ", MAX(" + dateCol + "), -12, MONTH))", F_Time, "#,0");
Make("Profit Rolling 12M", "CALCULATE([Gross Profit], DATESINPERIOD(" + dateCol + ", MAX(" + dateCol + "), -12, MONTH))", F_Time, "#,0");
// summary to Output window
Console.WriteLine("Created: " + created + ", Updated: " + updated);
This is better; they are neatly organised in folders and have formatting applied. All I need to do now is review them and save them. The only thing that didn’t work was the formatting.
Now that it worked, we’ll ask for some more measures:
// -----------------------------------------------------------------------------
// AdventureWorksDW 2022 – Extended Measure Library
// Creates ±35 measures for FactResellerSales with proper folders & formatting.
// -----------------------------------------------------------------------------
var table = Model.Tables["FactResellerSales"];
if (table == null) throw new Exception("Table 'FactResellerSales' not found.");
// helper delegate
System.Action<string,string,string> Make = (name, expr, folderPath) =>
{
var m = table.AddMeasure(name, expr);
m.DisplayFolder = folderPath;
};
// -----------------------------------------------------------------------------
// SALES PERFORMANCE
// -----------------------------------------------------------------------------
Make("Sales", "SUM(FactResellerSales[SalesAmount])", "Measures\\Sales Performance");
Make("Total Cost", "SUM(FactResellerSales[TotalProductCost])", "Measures\\Sales Performance");
Make("Gross Profit", "[Sales] - [Total Cost]", "Measures\\Sales Performance");
Make("Gross Margin %", "DIVIDE([Gross Profit],[Sales])", "Measures\\Sales Performance");
Make("Order Count", "DISTINCTCOUNT(FactResellerSales[SalesOrderNumber])","Measures\\Sales Performance");
Make("Units Sold", "SUM(FactResellerSales[OrderQuantity])", "Measures\\Sales Performance");
Make("Avg Order Value", "DIVIDE([Sales],[Order Count])", "Measures\\Sales Performance");
Make("Avg Unit Price", "DIVIDE([Sales],[Units Sold])", "Measures\\Sales Performance");
Make("Avg Unit Cost", "DIVIDE([Total Cost],[Units Sold])", "Measures\\Sales Performance");
Make("Profit per Unit", "DIVIDE([Gross Profit],[Units Sold])", "Measures\\Sales Performance");
Make("Profit per Order", "DIVIDE([Gross Profit],[Order Count])", "Measures\\Sales Performance");
// -----------------------------------------------------------------------------
// CUSTOMER INSIGHTS
// -----------------------------------------------------------------------------
Make("Distinct Customers", "DISTINCTCOUNT(FactResellerSales[CustomerKey])", "Measures\\Customer Insights");
Make("Sales per Customer", "DIVIDE([Sales],[Distinct Customers])", "Measures\\Customer Insights");
Make("Orders per Customer","DIVIDE([Order Count],[Distinct Customers])", "Measures\\Customer Insights");
Make("Units per Customer", "DIVIDE([Units Sold],[Distinct Customers])", "Measures\\Customer Insights");
Make("Profit per Customer","DIVIDE([Gross Profit],[Distinct Customers])", "Measures\\Customer Insights");
// -----------------------------------------------------------------------------
// PRODUCT PERFORMANCE
// -----------------------------------------------------------------------------
Make("Distinct Products", "DISTINCTCOUNT(FactResellerSales[ProductKey])", "Measures\\Product Performance");
Make("Sales per Product", "DIVIDE([Sales],[Distinct Products])", "Measures\\Product Performance");
Make("Profit per Product", "DIVIDE([Gross Profit],[Distinct Products])", "Measures\\Product Performance");
Make("Top Product Sales", "TOPN(1, VALUES('DimProductSubcategory'[EnglishProductSubcategoryName]), [Sales])", "Measures\\Product Performance");
Make("Sales Category Share","DIVIDE([Sales], CALCULATE([Sales], ALL('DimProductCategory')))", "Measures\\Product Performance");
Make("Profit Category Share","DIVIDE([Gross Profit], CALCULATE([Gross Profit], ALL('DimProductCategory')))", "Measures\\Product Performance");
// -----------------------------------------------------------------------------
// TIME INTELLIGENCE (requires DimDate marked as Date Table)
// -----------------------------------------------------------------------------
Make("Sales YTD", "TOTALYTD([Sales],'DimDate'[FullDateAlternateKey])", "Measures\\Time Intelligence");
Make("Profit YTD", "TOTALYTD([Gross Profit],'DimDate'[FullDateAlternateKey])", "Measures\\Time Intelligence");
Make("Sales PY", "CALCULATE([Sales],DATEADD('DimDate'[FullDateAlternateKey],-1,YEAR))", "Measures\\Time Intelligence");
Make("Profit PY", "CALCULATE([Gross Profit],DATEADD('DimDate'[FullDateAlternateKey],-1,YEAR))", "Measures\\Time Intelligence");
Make("Sales YoY %", "DIVIDE([Sales]-[Sales PY],[Sales PY])", "Measures\\Time Intelligence");
Make("Profit YoY %", "DIVIDE([Gross Profit]-[Profit PY],[Profit PY])", "Measures\\Time Intelligence");
Make("Sales MTD", "TOTALMTD([Sales],'DimDate'[FullDateAlternateKey])", "Measures\\Time Intelligence");
Make("Profit MTD", "TOTALMTD([Gross Profit],'DimDate'[FullDateAlternateKey])", "Measures\\Time Intelligence");
Make("Sales QTD", "TOTALQTD([Sales],'DimDate'[FullDateAlternateKey])", "Measures\\Time Intelligence");
Make("Profit QTD", "TOTALQTD([Gross Profit],'DimDate'[FullDateAlternateKey])", "Measures\\Time Intelligence");
Make("Sales Rolling 12M","CALCULATE([Sales],DATESINPERIOD('DimDate'[FullDateAlternateKey],MAX('DimDate'[FullDateAlternateKey]),-12,MONTH))","Measures\\Time Intelligence");
Make("Profit Rolling 12M","CALCULATE([Gross Profit],DATESINPERIOD('DimDate'[FullDateAlternateKey],MAX('DimDate'[FullDateAlternateKey]),-12,MONTH))","Measures\\Time Intelligence");
Now we have 4 sets of measures neatly placed in 4 folders.
Now i can review the numbers.
TMDL
TMDL is Microsoft’s YAML-based representation of a tabular model, which is fully editable, meaning you can define:
1. Tables
2. Columns
3. Measures
4. Relationships
5. Calculation groups
You can use it in the Tabular editor and Power BI Desktop.
Right-click on the Reseller table and select script TMDL to Script tab:
Here we can add in measures as follows, before the partition block.
Here, we can define the expression, the format string, and the display folder, which will live in the FactReseller table.
And it worked nicely.
