Automating DAX using C# to Create Dynamic Metrics on TOM Power BI Models in Visual Studio

Hey, everyone! In this blog post, I'm going to show you how to use C# to automate the generation of DAX measures in your Power BI models. This is a great way to speed up the process of building out your reporting and analysis infrastructure, and it can also help you avoid some common errors that can occur when creating DAX measures manually. So let's get started!

This is a great way to then also connect directly to Power BI reports from your Azure hosted Analysis Services Server models as these models are TOM models. Then in Power BI reports developers can directly connect to these models and build reports every time rather than building data models from scratch each time within each Power BI Desktop files.

Of course the Power BI Premium Service can be used to host and directly connect to Power BI TOM data models as well so that you can connect directly for those who are already making that level of Power BI capacity investment.

See the full documentation on Tabular Object Models (TOM) below.

https://docs.microsoft.com/en-us/analysis-services/tom/introduction-to-the-tabular-object-model-tom-in-analysis-services-amo?view=asallproducts-allversions

Import NuGet Packages needed for script, use the package manager available in your IDE.

using System;
using Microsoft.AnalysisServices.Tabular;

Define the name space and your Azure Analysis Server connection string. Once you have established a successful connection then point to the specific model that you want to connect to on the server.

namespace powerbi_console
{
class Program
{
static void Main(string[] args)
{
Server tabularServer = new Server();
tabularServer.Connect("asazure://aspaaseastus2.asazure.windows.net/yourservername");

Model model = tabularServer.Databases[3].Model;

Table table = model.Tables["yourtablename"];

string[] keys = *array of the keys that you want to pass*;

string[] modalities = *array of the modalities you want to pass*;

Write loops for new columns and metrics to create every time the script is run and save the new metrics to the hosted Azure Analysis Server model. If you want to re-write the measures every time you run the script you need to replace this model using the base model again.

So save a base model in a separate file, connection to the Azure Analysis Server instance there as well and push it every time before you run this script.

Measure SumofValue = new Measure();
SumofValue.Name = "Sum_of_Value";
SumofValue.Expression = "SUM(yourtablename[Value])";
table.Measures.Add(SumofValue);
model.SaveChanges();

foreach (Column column in table.Columns)
{
Console.WriteLine("Coulumn: " + column.Name);
}

foreach (Measure measure in table.Measures)
{
Console.WriteLine("Measure: " + measure.Name);
Console.WriteLine(measure.Expression);
}

foreach (string key in keys)
{
Measure newMeasure = new Measure();
newMeasure.Name = key;
newMeasure.Expression = "SUMX(FILTER(datamodel,datamodel[Indicator]=\""+key+"\"),([Sum_of_Value]))";
table.Measures.Add(newMeasure);
model.SaveChanges();
}

foreach (string key in keys)
{
Measure newMeasure = new Measure();
newMeasure.Name = key + " N";
newMeasure.Expression = "SUMX(FILTER(yourtablename,yourtablename[Indicator]=\"" + key + "\" && datamodel[Numerator]=\"N\"),([Sum_of_Value]))";
table.Measures.Add(newMeasure);
model.SaveChanges();
}

foreach (string key in keys)
{
Measure newMeasure = new Measure();
newMeasure.Name = key + " D";
newMeasure.Expression = "SUMX(FILTER(yourtablename,yourtablename[Indicator]=\"" + key + "\" && datamodel[Numerator]=\"D\"),([Sum_of_Value]))";
table.Measures.Add(newMeasure);
model.SaveChanges();
}
/*
foreach (string key in keys)
{
Measure newMeasure = new Measure();
newMeasure.Name = "Pos " + key ;
newMeasure.Expression = "SUMX(FILTER(yourtablename,yourtablename[Indicator]=\""+ key + "_POS"+ "\"),([Sum of Value]))";
table.Measures.Add(newMeasure);
model.SaveChanges();
}
*/
foreach (string modality in modalities)
{
Measure newMeasure1 = new Measure();
newMeasure1.Name = modality + " Tests";
newMeasure1.Expression = "SUMX(FILTER(yourtablename,yourtablename[Modality]=\"" + modality + "\"),([TST]))";
table.Measures.Add(newMeasure1);
model.SaveChanges();
}

foreach (string modality in modalities)
{
Measure newMeasure2 = new Measure();
newMeasure2.Name = modality + " Pos Tests";
newMeasure2.Expression = "SUMX(FILTER(yourtablename,yourtablename[Modality]=\"" + modality + "\"),([TST_POS]))";
table.Measures.Add(newMeasure2);
model.SaveChanges();
}

foreach (string modality in modalities)
{
Measure newMeasure3 = new Measure();
newMeasure3.Name = modality + " Yield%";
newMeasure3.Expression = "DIVIDE(["+modality+" Pos Tests],["+modality+" Tests])";
table.Measures.Add(newMeasure3);
model.SaveChanges();
}

}
}
}