Cube.AddMeasureColumn

Understanding Cube Measures

Before we dive into the M code behind Cube.AddMeasureColumn, it’s important to understand what a cube measure is. In a cube, a measure is a calculation that is performed on a set of data. For example, you may have a sales cube that includes measures such as total sales, average sales, and maximum sales. These measures can be used to analyze the data in different ways, such as by region, product line, or salesperson.

When you add a measure to a cube, you can use it to create a calculated column in Power Query. This is where the Cube.AddMeasureColumn function comes in. It allows you to create a calculated column based on a measure in your cube.

The M Code Behind Cube.AddMeasureColumn

To use Cube.AddMeasureColumn, you need to understand the M code behind it. The function takes four arguments:

1. Cube: This is the name of the cube that contains the measure you want to use. For example, if your sales data is stored in a cube called “Sales Data,” you would use “Sales Data” as the cube argument.

2. MeasureName: This is the name of the measure you want to use. For example, if you want to create a calculated column based on the total sales measure, you would use “Total Sales” as the MeasureName argument.

3. ColumnPrefix: This is the prefix that will be added to the name of the new calculated column. For example, if you use “Sales” as the ColumnPrefix argument, the new column will be called “Sales Total Sales.”

4. ColumnSuffix: This is the suffix that will be added to the name of the new calculated column. For example, if you use “Amount” as the ColumnSuffix argument, the new column will be called “Total Sales Amount.”

The M code for Cube.AddMeasureColumn looks like this:

Cube.AddMeasureColumn = (Cube as text, MeasureName as text, ColumnPrefix as text, ColumnSuffix as text) =>

let

Source = Cube.CubeFunctions(Cube, [Query = “MDSCHEMA_MEASURES”]),

FilteredRows = Table.SelectRows(Source, each ([CUBE_NAME] = Cube and [MEASURE_NAME] = MeasureName)),

MeasureFormula = if Table.IsEmpty(FilteredRows) then “” else FilteredRows{0}[MEASURE_EXPRESSION],

ColumnName = ColumnPrefix & ” ” & MeasureName & ” ” & ColumnSuffix,

NewColumn = if MeasureFormula = “” then null else Table.AddColumn(Source, ColumnName, each Expression.Evaluate(MeasureFormula, #shared)),

RemovedColumns = Table.RemoveColumns(NewColumn,{“CUBE_NAME”, “MEASURE_NAME”, “MEASURE_UNIQUE_NAME”})

in

RemovedColumns

Cube.AddMeasureColumn(“Sales Data”, “Total Sales”, “Sales”, “Amount”)