Now for something completely different

Published: | Updated:
Picture of Daniel Otykier
CTO

Daniel Otykier is the creator and CTO of Tabular Editor and a Microsoft Data Platform MVP. He’s in charge of ensuring that we continue to offer the best possible solution to our users, while also leading our skilled team of core developers. 

Finally, add a measure to the calculated table, to provide the currently selected WhatIf parameter value. This is the measure you’re going to use in Power BI, to pull the WhatIf parameter into your WhatIf scenarios. If you named both your calculated table and calculated table column “MyParam”, you should name this measure “MyParam Value” and use the expression: SELECTEDVALUE(‘MyParam’[MyParam], 50) where 50 is the default value to use, in case multiple values/nothing is selected on the WhatIf slicer.

var server = "te3synapsetest-ondemand.sql.azuresynapse.net";
var database = "AdventureWorksDW";
var applicationId = "e449df3a-957e-40ff-802f-e3fa75e43be1";
var secret = "<your-service-principal-secret-key>";

// Part 1: Create legacy data source
var source = Model.AddDataSource();
source.Provider = "System.Data.OleDb";
source.ConnectionString = string.Format("Provider=MSOLEDBSQL;Data Source={0};Initial Catalog={1};Authentication=ActiveDirectoryServicePrincipal;User ID={2};Password={3}",
    server, database, applicationId, secret);

// Part 2: Swap partitions
string mExpression;

foreach(var table in Model.Tables)
{
    // Loop through all tables that have at least one M partition, or an M Source Expression:
    var partition = table.Partitions.FirstOrDefault() as MPartition;
    if (partition != null) mExpression = partition.MExpression;
    else if (!string.IsNullOrEmpty(table.SourceExpression)) mExpression = table.SourceExpression;
    else continue;

    // Extract the schema and name of the source table/view from the M Expression:
    string pattern = @"\[Schema=""(?<schema>.*?)"",Item=""(?<item>.*?)""\]";
    Match match = Regex.Match(mExpression, pattern);
    var sourceObjectName = string.Format("[{0}].[{1}]", match.Groups["schema"], match.Groups["item"]);
    
    // Create a single legacy SQL partition with a "SELECT * FROM <sourceObjectName>" query:
    var legacyPartition = table.AddPartition(query: "SELECT * FROM " + sourceObjectName);
    legacyPartition.DataSource = source;
    
    // Delete all other partitions:
    foreach(var p in table.Partitions.Where(p => p != legacyPartition).ToList()) p.Delete();
}

// Part 3: Perform schema check
SchemaCheck(Model);
git add .
git commit -m "Initial commit of code base"
git push
// Modify below to point to the file that holds your WhatIf parameter settings:
var settings = System.IO.File.ReadLines(@"c:\WhatIf\WhatIfSettings.csv");
var c = System.Globalization.CultureInfo.InvariantCulture;
foreach(var setting in settings)
{
    var settingArray = setting.Split(',');
    // Parameter settings:
    var paramName = settingArray[0];
    var paramMin = decimal.Parse(settingArray[1],c);
    var paramMax = decimal.Parse(settingArray[2],c);
    var paramIncrement = decimal.Parse(settingArray[3],c);
    var paramDefault = decimal.Parse(settingArray[4],c);
    var paramDataType = (DataType)Enum.Parse(typeof(DataType), settingArray[5]);
    // Add a new calculated table to the model:
    var table = Model.AddCalculatedTable(paramName,
        string.Format(c, "GENERATESERIES({0},{1},{2})", paramMin, paramMax, paramIncrement));
    // Add the Calculated Table column and set SummarizeBy to None:
    var column = table.AddCalculatedTableColumn(paramName, "[Value]", "", paramDataType);
    column.SummarizeBy = AggregateFunction.None;
    // Set Extended Property on the Calculated Table Column:
    column.SetExtendedProperty("ParameterMetadata", "{\"version\":0}", ExtendedPropertyType.Json);
    // Add the WhatIf parameter measure:
    table.AddMeasure(paramName + " Value",
        string.Format(c, "SELECTEDVALUE({0}, {1})", column.DaxObjectFullName, paramDefault));
}

Related articles