Developing DAX Measures and Security Roles

This chapter details the implementation of DAX measures and security roles for the dataset developed in the previous two chapters. We first create a set of base measures for each business process that represents business definitions such as gross and net sales, cost of sales, and margin percentages. These base measures are then leveraged in the development of date intelligence calculations including year-to-date (YTD) and year-over-year (YOY) growth. Additionally, a set of custom measures is created, including exceptions, rankings, and KPI targets, to further extract insights from the dataset and simplify report visualizations.

This chapter also contains examples of dynamic security models in which the identity of the logged-in user is used to filter the dataset. Finally, guidance is provided on testing the performance of DAX expressions with DAX Studio.

In this chapter, we’ll review the following topics:

• DAX measure...

DAX measure basics

All analytical expressions ranging from simple sums and averages to custom, complex statistical analyses should be implemented within DAX measures. Although it’s technically possible to utilize the default summarization property of columns for some basic measures, well-developed datasets should embed calculation logic into DAX measure definitions thus improving clarity and reusability.

The need for the rapid deployment of complex yet efficient and manageable DAX measures, queries, and security roles underscores earlier guidance regarding the value of an experienced Power BI dataset developer to deliver enterprise-grade solutions. Organizations are strongly advised to appreciate DAX as a functional programming language (not just Excel formulas) that is central to Power BI solutions and thus take steps to ensure that Power BI dataset developers possess the required knowledge and skills with DAX. These steps may involve certification exams, detailed technical...

Base measures

Before any custom or complex DAX measures can be developed, a set of relatively simple base measures must be implemented first. These measures represent the metrics from the Define the facts section of Chapter 1, Planning Power BI Projects, and thus contain validated and approved business definitions.

For Adventure Works, a set of base measures related to sales, cost, and margins are applicable to both the Internet Sales and Reseller Sales fact tables, such as the following:

Reseller Gross Sales = SUMX('Reseller Sales', 'Reseller Sales'[UnitPrice] * 'Reseller Sales'[OrderQuantity])
Reseller Sales Discounts = SUM('Reseller Sales'[DiscountAmount])
Reseller Net Sales = [Reseller Gross Sales] - [Reseller Sales Discounts]
Reseller Sales Product Cost = SUMX('Reseller Sales', 'Reseller Sales'[OrderQuantity] * 'Reseller Sales'[ProductStandardCost])  
Reseller Sales Freight Cost = SUM('Reseller...

Date intelligence metrics

Date intelligence metrics are typically the first set of measures to be added to a dataset following base measures. These measures reference the base measures and add a custom filtering condition to the Date dimension table, thus providing visibility to multiple distinct time intervals, such as year-to-date and the previous year-to-date.

Given their built-in date filtering logic, Power BI reports and dashboards can be developed faster and without manual maintenance costs of updating date filter conditions.

The following four measures apply custom filter contexts to either return the current year, month, and week by default, or the latest of these time intervals given the date filters applied in a report:

Internet Net Sales (CY) = CALCULATE([Internet Net Sales],FILTER(ALL('Date'), 'Date'[Calendar Year] = MAX('Date'[Calendar Year]) && 'Date'[Date] >= MIN('Date'[Date]) && &apos...

Calculation groups

Calculation groups are a data modeling feature that enable common expression logic to be centralized and leveraged by other measures when needed in reports. In this section, we cover the creation of the same basic date intelligence from the previous section, Date intelligence metrics, but use calculation groups.

In the previous section, we covered the creation of basic date intelligence metrics for Internet Net Sales. However, supporting eight common date intelligence expressions for each of 24 base measures would imply adding 192 (8*24) distinct measures to the dataset, thus adding both development time and complexity for report authors and analysts. Calculation groups address this issue by allowing report authors to reuse common expressions such as year-to-date for whichever base measure it’s needed for.

Calculation groups allow the creation of general calculation formulas that can be applied to any explicit measure within the data model. Thus...

Dimension metrics

The majority of DAX measures apply aggregating functions to numeric columns of fact tables. However, several of the most important metrics of a dataset are those that focus on dimension tables, such as the count of customers who’ve purchased and those who haven’t.

It can also be necessary to count the distinct values of a dimension column such as the number of postal codes sold to or the number of distinct marketing promotions over a period of time.

In the dataset for this project, the customer dimension table is exclusive to the Internet Sales fact table, and the measure should only count customers with internet sales history.

Additionally, slowly changing dimension logic has been implemented so that a single customer defined by the CustomerAlternateKey column could have multiple rows defined by the CustomerKey column.

The following two DAX measures count the number of unique customers and products with internet sales history:

...

Ranking metrics

Many reports and analyses are built around the ranking of dimensions relative to measures, such as the top 10 salespeople based on YTD sales. Ranking measures can also help deliver cleaner and more intuitive report visualizations as they substitute small integer values for large numbers and decimal places. Ranking measures can be as simple as specifying a column and a measure, or more complex with unique ranking logic applied in distinct filter contexts.

Ranking measures in DAX are implemented via the RANKX() function, which is an iterator like SUMX() and FILTER(). As an iterating function, two required input parameters include a table and the expression to be evaluated for each row of the table. The following two measures rank products based on the Internet Net Sales measure:

Internet Net Sales Product Rank = RANKX(ALL('Product'[ProductAlternateKey]),[Internet Net Sales],,DESC,Skip)

Internet Net Sales Product Rank (All Products) = 
    VAR __ProdRankTable...

Security roles

In addition to DAX’s utility for creating measures, DAX is also required when defining security roles within a dataset. Per Chapter 1, Planning Power BI Projects, the required data security for this project is to limit the visibility of the Adventure Works sales team users to their respective sales territory groups. There are three sales territory groups (North America Sales Group, Europe Sales Group, and Pacific Sales Group), and, as described in the previous chapter, cross-filtering relationships exist between the Sales Territory dimension table and all three fact tables (Internet Sales, Reseller Sales, and Sales and Margin Plan).

Therefore, security roles with a filter condition on the given sales territory group also filter the fact tables, and business users mapped to these roles only see data associated with their Sales Territory group.

Security roles are defined in Power BI Desktop via the Manage roles dialog of the Modeling tab as shown in Figure...

Performance testing

Given that DAX measures can implement complex business logic and are dynamically calculated as users interact with reports, the performance of these calculations is a critical component of providing a good user experience.

There are often many available methods of implementing business logic and custom filter contexts into DAX measures. Although these alternatives deliver the essential functional requirements, they can have very different performance characteristics, which can ultimately impact user experience and the scalability of a dataset.

When migrating a self-service dataset to a corporate solution or preparing a large and highly utilized dataset, it’s always a good practice to test common queries and the DAX measures used by those queries.

For example, the same common dimension grouping (for example, Product Category and Year) and the same filter context (Year = 2018) could produce dramatically different performance results based on the...

Summary

This chapter developed and described several common classes of DAX measures, including base measures, date intelligence metrics, dimension metrics, and ranking metrics. These measures utilized the fact and dimension tables developed in previous chapters.

In addition to detailed measure examples, the primary concepts of the DAX were reviewed and standard row-level security (RLS) and dynamic RLS (DRLS) models were shared. Finally, performance testing and tuning tools, including the Performance analyzer pane and DAX Studio were presented.

In the following chapter, Power BI reports are created, which leverage the dataset that has been incrementally developed since Chapter 2 and 3. Report-authoring features, such as the visualization types in Power BI Desktop, access the DAX measures from this chapter and the dimensions from previous chapters to deliver business insights and intuitive, self-service functionality.

Join our community on Discord

Join our community...