3E. Add measures to Power BI Desktop models

Question 1

What’s the difference between implicit and explicit measures?

Answer 1

Measures in Microsoft Power BI models are either implicit or explicit. Implicit measures are automatic behaviors that allow visuals to summarize model column data. Explicit measures, also known simply as measures, are calculations that you can add to your model. This module focuses on how you can use implicit measures.

Question 1

What does the sigma symbol signify next to a column in the Fields pane, and how can you control whether a column has this symbol?

Answer 1

• It’s a numeric column.
• It will summarize column values when it is used in a visual (when added to a field well that supports summarization).

As a data modeler, you can control if and how the column summarizes by setting the Summarization property to Don’t summarize or to a specific aggregation function. When you set the Summarization property to Don’t summarize, the sigma symbol will no longer show next to the column in the Fields pane.

The sigma symbol does not show next to non-numeric columns in the Fields pane because they don’t summarize by default.

Question 2

Which aggregation functions are supported by numeric columns?

Answer 2

Sum
Average
Minimum
Maximum
Count (Distinct)
Count
Standard deviation
Variance
Median

Question 3

Which aggregation functions are supported by non-numeric columns?

Answer 3

Text columns allow the following aggregations:

First (alphabetically)
Last (alphabetically)
Count (Distinct)
Count

Date columns allow the following aggregations:

Earliest
Latest
Count (Distinct)
Count

Boolean columns allow the following aggregations:

Count (Distinct)
Count

Question 4

What are some benefits of relying on implicit measures?

Answer 4

Implicit measures are simple concepts to learn and use, and they provide flexibility in the way that report authors visualize model data.
Additionally, they mean less work for you as a data modeler because you don’t have to create explicit calculations.

Question 5

What are some limitations of implicit measures?

Answer 5

• Despite setting an appropriate summarization method, report authors could choose to aggregate a column in unsuitable ways. For example, in the matrix visual, you could modify the aggregate function of Unit Price to Sum. The report visual obeys your setup, but it has now produced a Sum of Unit Price column, which presents misleading data.
• The most significant limitation of implicit measures is that they only work for simple scenarios, meaning that they can only summarize column values that use a specific aggregation function. Therefore, in situations when you need to calculate the ratio of each month’s sales amount over the yearly sales amount, you’ll need to produce an explicit measure by writing a Data Analysis Expressions (DAX) formula to achieve that more sophisticated requirement.
• Implicit measures don’t work when the model is queried by using Multidimensional Expressions (MDX). This language expects explicit measures and can’t summarize column data. It’s used when a Power BI semantic model is queried by using Analyze in Excel or when a Power BI paginated report uses a query that is generated by the MDX graphical query designer.

Question 6

Which query language is incompatible with implicit measures?

Answer 6

Implicit measures don’t work when the model is queried by using Multidimensional Expressions (MDX). This language expects explicit measures and can’t summarize column data. It’s used when a Power BI semantic model is queried by using Analyze in Excel or when a Power BI paginated report uses a query that is generated by the MDX graphical query designer.

Question 7

How are measures stored?

Answer 7

Measures don’t store values in the model. Instead, they’re used at query time to return summarizations of model data.

Question 8

Can measures reference tables or columns directly?

Answer 8

No. measures can’t reference a table or column directly; they must pass the table or column into a function to produce a summarization.

Question 9

What is a “simple” measure, and how do they compare to implicit measures?

Answer 9

A simple measure is one that aggregates the values of a single column (or a single table); it does what implicit measures do automatically.

Question 10

What is a good step to take immediately after creating a measure?

Answer 10

Immediately after you create a measure, set the formatting options to ensure well-presented and consistent values in all report visuals.

Question 11

What is a benefit of using only explicit measures and hiding implicit measures from the view of report authors?

Answer 11

It results in report authors losing their ability to summarize the column except by using your measures, meaning they cannot accidentally summarize columns in illogical ways.

Question 12

Describe the COUNT function.

Answer 12

The COUNT DAX function counts the number of non-BLANK values in a column,

Question 13

Describe the DISTINCTCOUNT function.

Answer 13

The DISTINCTCOUNT DAX function counts the number of distinct values in a column.

Question 14

What is a sometimes semantically clearer alternative to COUNT?

Answer 14

Instead of counting values in a column, it’s semantically clearer to use the COUNTROWS DAX function. Unlike the previously introduced aggregation functions, which aggregate column values, the COUNTROWS function counts the number of rows for a table.

Question 15

What are compound measures?

Answer 15

When a measure references one or more measures, it’s known as a compound measure.

Question 16

How do calculated columns and measures compare in terms of performance, and which might be preferable?

Answer 16

Removing calculated columns and keeping only a corresponding measure results in a decreased semantic model size and shorter data refresh times.

Question 17

What are quick measures?

Answer 17

Microsoft Power BI Desktop includes a feature named Quick Measures. This feature helps you to quickly perform common, powerful calculations by generating the DAX expression for you.

Many categories of calculations and ways to modify each calculation are available to fit your needs. Moreover, you are able to see the DAX that’s generated by the quick measure and use it to jumpstart or expand your DAX knowledge.

After the quick measure has been created, you must apply any changes in the formula bar.

Question 18

How are measures and calculated columns similar?

Answer 18

Both are:
- Calculations that you can add to your semantic model.
- Defined by using a DAX formula.
- Referenced in DAX formulas by enclosing their names within square brackets.

Question 19

How do calculated columns and measures differ?

Answer 19

• Purpose - Calculated columns extend a table with a new column, while measures define how to summarize model data.
• Evaluation - Calculated columns are evaluated by using row context at data refresh time, while measures are evaluated by using filter context at query time. Filter context is introduced in a later module; it’s an important topic to understand and master so that you can achieve complex summarizations.
• Storage - Calculated columns (in Import storage mode tables) store a value for each row in the table, but a measure never stores values in the model.
• Visual use - Calculated columns (like any column) can be used to filter, group, or summarize (as an implicit measure), whereas measures are designed to summarize.