## 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

``` This code defines a function called Cube.AddMeasureColumn, which takes the four arguments we discussed earlier. Let's take a closer look at how the code works. The first line of the code defines the function and its arguments. The next line uses the Cube.CubeFunctions function to get information about the measures in the cube. This information is stored in a table called Source. The next line filters the rows in the Source table to find the row that corresponds to the measure we want to use. This is done by checking the CUBE_NAME and MEASURE_NAME columns against the Cube and MeasureName arguments. If a row is found, the MeasureFormula variable is set to the MEASURE_EXPRESSION column of that row. This is the actual calculation used by the measure. The ColumnName variable is set to the value of ColumnPrefix, MeasureName, and ColumnSuffix concatenated together. This will be the name of the new calculated column. The NewColumn variable is set to a new table that includes the Source table with the new calculated column added. The calculation for the new column is performed using Expression.Evaluate, which evaluates the MeasureFormula for each row in the table. Finally, the RemovedColumns variable is set to the NewColumn table with the CUBE_NAME, MEASURE_NAME, and MEASURE_UNIQUE_NAME columns removed. This is the final result of the Cube.AddMeasureColumn function. Using Cube.AddMeasureColumn in Power Query Now that we understand the M code behind Cube.AddMeasureColumn, let's see how it can be used in Power Query. Here's an example of how to use the function to create a calculated column based on the total sales measure from a Sales Data cube: ```

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

``` This code will create a new calculated column called "Sales Total Sales Amount" that is based on the "Total Sales" measure in the "Sales Data" cube. You can use this function to create calculated columns based on any measure in a cube. By using different values for the MeasureName, ColumnPrefix, and ColumnSuffix arguments, you can create a wide variety of calculated columns. The Cube.AddMeasureColumn function is a powerful tool that can help you analyze your data in new ways. By understanding the M code behind the function, you can use it to create calculated columns based on measures in your cube. This can help you gain new insights into your data and make more informed decisions. Power Query and M Training Courses by G Com Solutions (0800 998 9248) Power Query and M Intensive Training Course £1,050.00 – £26,550.00 Select optionsContinue Loading Done Power Query and M Introduction £395.00 – £9,750.00 Select optionsContinue Loading Done Power Query and M Intermediate £395.00 – £9,750.00 Select optionsContinue Loading Done Power Query and M Advanced £395.00 – £9,750.00 Select optionsContinue Loading Done Upcoming Courses Contact Us Subject Your Name (required) Company/Organisation Email (required) Telephone Training Course(s) Power BI Intensive TrainingPower BI introduction Power BI IntermediatePower BI AdvancedDAXPower Query MPower BI CertificationPower BI AdministrationPower PlatformPower AutomatePower AppsOTHER Your Message Upload Example Document(s) (Zip multiple files) ```
``` ```
``` ```
``` ```
``` ```
``` ```