DAX Methods Used in PowerPivot and Power BI

Modified on Sat, 6 Jul at 10:18 PM

Overview of DAX Methods for PowerPivot and Power BI

We hired the best in the business - Marco Russo to lead our DAX strategy. As a result, we have the most efficient and accurate DAX methodology we could possibly provide, and you can find more information by visiting his DAX Patterns website where he and partner Alberto Ferrari have published their works online.

DAX Game Plan

We followed these strategies:

Use a methodology that is well known in the public sphere, so advanced users can use public reference materials for understanding and embellishment - rather than rely on a closed system.
Provide a wide variety of measure functions:
- pattern measures where you can create graphs and cumulative visualizations
- specific "to-date" measures e.g. month-to-date, last year- to-date
- A modicum of standard business metrics out of the box - with the ability to expand in future versions
Use naming conventions that are consistent and help customers easily find what they are looking for

Measure Types

Here is a list of measure categories which exist in QQube Version 10 for Power Pivot and Power BI

QQube FACT Fields. A QQube FACT table contains fields you can measure, but a calculation must be performed ON the field in order to make it a measure - so by default they are not used and hidden in every Power Pivot and Power BI data model.
.Base Measures. These are measures that are prefixed with a period (".") and are generally a summation of the raw QQube fields. There are no filters in these measures and are available for use for any analytic.
Intermediate Measures. These are internal measures and contain two types of calculations: (a) One to assess "Today Date" from QQube, or last date in a FACT table, and (b) A specific DAX Calculation for a Calendar Pattern Measure.
Hierarchy Measures. Present in financial statement, job profit and loss, and trial balance related data models (including Profit and Loss Detail) to address ragged hierarchies of financial groupings and account levels.
Related Field Measures. Only used in a handful of cases, these are measures that grab numerical information from a DIMENSION e.g. Days Past Due from the Document Attributes Folder in the Accounts Payable Data Model.
Calendar Pattern Measures. Based upon the DAX Pattern Measures, these allow easy graphing for Cumulative by Year, Quarter, Month, Week. For Power Pivot they are great for seeing columns of like data side by side, such as year, or month and will give proper grand total columns. We also include growth rate measures such as year-over-year (YOY).
Calendar Specific Measures. Whereas Calendar Pattern Measures allow for equal buckets over a long period of time, Specific Calendar Measures speak to a specific and narrow period of time, e.g. Month-To-Date, Year-To-Date, Last Month, Yesterday, etc.
Comparison Measures. Subtracting one specific calendar measure from another, % difference, to make it easier for the end-user.
Single Value Measures. Specific measures which don't follow a calendar pattern, nor are segmented into specific calendar buckets. e.g. Days out of Stock

Measure Categories

Power BI allows us the option of segmenting a list of Measures into Folders, to help customers quickly find their fields.

Certain Measure Categories exist in almost every data model as highlighted in yellow below:

Automatically hidden (as shown in red):

Dimension Links. These are the links to the dimensions which exist in a QQube FACT table.
Hierarchy Measures. These are "contributory" technical measures and are not used in any visual or report.
Intermediate Measures. Like Hierarchy Measures, these are "contributory" technical measures - or reusable code - which are not used in any visual or report.
Related Field Measures. Like Hierarchy and Intermediate Measures these are not intended to be used as part of a visual or report.
QQube FACT Fields. QQube Raw Fields upon which measures are created.