Goal

Automating the creation of comparison metrics in Power BI to save time and effort when generating performance reports.

Problem

Manually creating multiple comparison measures for KPIs in Power BI is time-consuming and repetitive, especially when dealing with a large number of KPIs. For each KPI, several measures need to be created to calculate values and differences for different time periods.

Solution

Utilizing Tabular Editor and a C# script macro to automate the process of generating comparison measures for KPIs in Power BI.

Solution Details:

1. Tabular Editor Introduction: Tabular Editor is a tool that facilitates working with Tabular models, such as those used in Power BI. It allows for efficient manipulation of measures, tables, relationships, and other components of the model.
   • URL: https://www.sqlbi.com/tools/tabular-editor/
2. Integration with Power BI: Tabular Editor can be integrated with Power BI as an external tool. This allows you to access its features directly from the Power BI interface.
   • You can find the extension under “External Tools” on the top ribbon of Power BI.
3. C# Script Macro: To automate the process of creating comparison measures, a C# script macro is used. This macro can be applied to any measure within your Power BI model.
4. Macro Execution Process:
   • The macro iterates through the selected columns (KPIs) for which comparison measures are needed.
   • For each selected column, a new measure is added to the corresponding table.
   • The measure’s logic is defined using the DAX (Data Analysis Expressions) language to calculate the desired values based on specific conditions and relationships.
5. Example Macro:
   • Prerequisite 1: For measures in relation to a secondary dates table, duplicate your dates table, call it ‘Comparison Dates’ and rename the Dates column to ‘Baseline Date’. You should have an inactive relationship between ‘Dates’ and ‘Baseline Dates’.
   • Prerequisite 2: Create the following measure within the ‘Dates’ Table for time last year measures. Date difference = DATEDIFF (FIRSTDATE(Dates[Date]), LASTDATE (Dates[Date]),DAY) +1

This macro creates the following measures:

• Gross Revenue (CP): KPI value any comparison period
• Gross Revenue ∆± (CP): The nominal difference between this period and the comparison period
• Gross Revenue ∆% (CP): The percentage difference between this period and the comparison period
• Gross Revenue (LP): KPI value this time last period
• Gross Revenue ∆± (LP): The nominal difference between this period and this time last period
• Gross Revenue ∆% (LP): The percentage difference between this period and this time last period
• Gross Revenue (LY): KPI value this time last year
• Gross Revenue ∆± (LY): The nominal difference between this period and this time last year
• Gross Revenue ∆% (LY): The percentage difference between this period and this time last year

//Creating multiple measures for any selected measure

foreach(var c in Selected.Measures) //Change to Selected.Columns to create measures based of column
{
var newMeasure=c.Table.AddMeasure(
    c.Name+" (CP)", //Name
    "CALCULATE("+c.DaxObjectFullName+",ALL(Dates),USERELATIONSHIP('Dates'[Date],'Comparison Dates'[Baseline Date]))",//DAX Expression
    c.DisplayFolder //Display Folder
    );
var newMeasure2=c.Table.AddMeasure(
    c.Name+" ∆% (CP)",
    "("+c.DaxObjectFullName+"-["+c.Name+" (CP)])/["+c.Name+" (CP)]",
    c.DisplayFolder
    );
var newMeasure3=c.Table.AddMeasure(
    c.Name+" ∆± (CP)",
    c.DaxObjectFullName+"-["+c.Name+" (CP)]",
    c.DisplayFolder
    );    
var newMeasure4=c.Table.AddMeasure(
    c.Name+" (LP)",
    "CALCULATE("+c.DaxObjectFullName+",DATEADD(Dates[Date],-Dates[Date difference],DAY))",
    c.DisplayFolder
    );
var newMeasure5=c.Table.AddMeasure(
    c.Name+" ∆% (LP)",
    "("+c.DaxObjectFullName+"-["+c.Name+" (LP)])/["+c.Name+" (LP)]",
    c.DisplayFolder
    );
var newMeasure6=c.Table.AddMeasure(
    c.Name+" ∆± (LP)",
    c.DaxObjectFullName+"-["+c.Name+" (LP)]",
    c.DisplayFolder
    );
var newMeasure7=c.Table.AddMeasure(
    c.Name+" (LY)",
    "CALCULATE("+c.DaxObjectFullName+",DATEADD(Dates[Date],-1,YEAR))",
    c.DisplayFolder
    );
var newMeasure8=c.Table.AddMeasure(
    c.Name+" ∆% (LY)",
    "("+c.DaxObjectFullName+"-["+c.Name+" (LY)])/["+c.Name+" (LY)]",
    c.DisplayFolder
    );
var newMeasure9=c.Table.AddMeasure(
    c.Name+" ∆± (LY)",
    c.DaxObjectFullName+"-["+c.Name+" (LY)]",
    c.DisplayFolder
    ); 
}

Benefits:

• Automating the process of creating comparison measures significantly reduces the time and effort required to generate performance reports.
• The script handles the repetitive task of creating multiple measures for various time periods, resulting in consistent and accurate calculations.

In summary, the goal of the blog is to introduce a solution for automating the creation of comparison metrics in Power BI using Tabular Editor and C# script macros. The problem being addressed is the time-consuming nature of manually creating multiple measures for various KPIs and time periods. The provided solution streamlines this process, ultimately saving time and effort for users engaged in performance reporting using Power BI.