Tabular Editor 2 Scripts for PBI
/*---------------------------------
| Title: |
| C# Tabular Editor - DAX template |
----------------------------------
| Author: |
| Imran Haq, PBI Queryous |
| https://github.com/PBIQueryous |
| STAY QUERYOUS PBIX CHAMPS! |
---------------------------------*/
/* SCRIPT NOTES
*--------------------------------------------------*
| Inspiration and Credits: |
| PowerBI.Tips Team | https://powerbi.tips/ |
| Daniel Otykier | twitter.com/DOtykier |
| and endless more names from the PBI community, |
| Thank you all! |
*--------------------------------------------------*
*/
/* DESCRIPTION
* -----------------------------------
* This script, when executed, will loop through the currently selected measure(s),
* creating a series of measure(s) declared in the script below.
*
* e.g., from Model select [Measure] where Measure = SUM( tbl[column] ) or COUNT( tbl[column] )
* -----------------------------------
*/
// C# measure formula template:
// m.Table.AddMeasure( "MeasureName", "Expression", m.DisplayFolder);
/**** C# SCRIPT START ****/
// SET VARIABLES
// Quotation Character - helpful for wrapping " " around a text string within the DAX code
const string qt = "\"";
// Number Formatting Strings
var GBP0 = qt + "£" + qt + "#,0.0";
var GBP2 = qt + "£" + qt + "#,0.00";
var Whole = "#,0";
var Percent = "0.0 %";
var Decimal = "#,0.0";
var Number = "#,0";
var Currency0 = GBP0+";" +"-"+GBP0+";" +GBP0;
var Currency2 = GBP2+";" +"-"+GBP2+";" +GBP2;
var Deviation = "+"+Decimal+";" +"-"+Decimal+";"+ Decimal;
var Variance = "▲ 0.0%;▼ 0.0%;0.0%"
// Var RETURN text strings
var vResult = "var _result = ";
var rReturnRes = "RETURN" + '\n' + "_result";
var rReturn = "RETURN" + '\n';
var ifnotBlank = '\t' + "// IF( NOT ISBLANK( ";
var thenResult = " ) , _result )";
var rResult = '\t' + "_result";
// MeasureName Variables
var snap = " | SNAP";
// Var Measure Folder
var subFolder = "_Measures\\SubFolder";
// Script Variable: Creates a series of time intelligence measures for each selected measure in the Model
foreach(var m in Selected.Measures)
{
/***************************************** MeasureStart ************************************/
// Measure1: SUM
var m1 = m.Table.AddMeasure
(
// startSubScript
// MeasureName
m.Name + snap,
// DAX comment string
'\n' + "// snapshot - basic sum "
/* DAX expression START */
// DAX Variables
// Return Result
+ '\n' + vResult + m.DaxObjectName + '\n'
+ '\n' + rReturn
+ '\n' + rResult
// optional in DAX
// useful in cumulative measures - returns blank if no value exists for future dates
+ ifnotBlank + m.DaxObjectName + thenResult
);
/* DAX expression END */
// Metadata
// Display Folder (default - same folder as selected)
m1.DisplayFolder
// Optional: new Folder name below
= subFolder
;
// Provide some documentation
m1.Description = "From: " + m.Name + " - " + '\n' +
// Type metadata text here
"base measure; basic sum; forms the reference to subsequent time-intelligence measures"
;
m1.FormatString = Currency2
;
// endSubScript
/**************************************** MeasureEnd **************************************/
}
/**** C# SCRIPT END ****/