Tabular Editor: How to use advanced scritpting to create multiple measures based on one value and one category column

Question

I am trying to create multiple measures in Power BI through advanced scripting in Tabular editor that would replicate a Calculate(Sum(ColA),Keepfilters(ColB=i))

I understand that i would need a for loop and iterate over the values in ColB but i dont manage the iterating over the values in ColB.

How do i do that?

Answer 1

There is an ExecuteReader method available in Tabular Editor scripts, which returns an IDataReader-object that you can use to iterate the result. To add one measure for every value in a column, you would need to do something like this (inspired by this example):

using(var reader = ExecuteReader("EVALUATE VALUES(MyTable[ColB])");
{
    while(reader.Read())
    {
        var colBvalue = reader.GetString(0); // Assumes [ColB] contains strings, otherwise use .GetInt64, .GetDecimal, .GetDouble, etc...
        var measureName = string.Format("Measure filtered by {0}", colBvalue);
        var measureDax = string.Format("CALCULATE(SUM(MyTable[ColA]), KEEPFILTERS(MyTable[ColB]=\"{0}\"))", colBvalue);
        Model.Tables["MyTable"].AddMeasure(measureName, measureDax);
    }
}