Defining the facts

The facts represent the numeric columns to be included in the fact table. While the dimension columns from step 3 will be used for relationships to dimension tables, the fact columns will be used in measures containing aggregation logic such as the sum of a quantity column and the average of a price column:

  • Define the business logic for each fact that will be represented by measures in the dataset:
    • For example, gross sales are equal to the extended amount on a sales order, and net sales are equal to gross sales minus discounts
  • Any existing documentation or relevant technical metadata should be reviewed and validated
  • Similar to the dimensions, any conflicts between existing definitions should be addressed so that a single definition for a core set of metrics is understood and approved 
  • Additionally, a baseline or target source should be identified to validate the accuracy of the metrics to be created.
    • For example, several months following the project, it should be possible to compare the results of DAX measures from the Power BI dataset to an SSRS report or a SQL query
    • If no variance exists between the two sources, the DAX measures are valid and thus any doubt or reported discrepancy is due to some other factor
See Chapter 8, Connecting to Sources and Transforming Data with M, Chapter 9, Designing Import and DirectQuery Data Models, and Chapter 10, Developing DAX Measures and Security Roles, for details on the fact table columns to include in Power BI datasets (for import or DirectQuery) and the development of DAX metric expressions. The fact definitions from this step relate closely to the concept of base measures described in Chapter 10, Developing DAX Measures and Security Roles. Ultimately, the DAX measures implemented have to tie to the approved definitions, but there are significant data processing, storage and performance implications based on how this logic is computed. In many cases, the Power BI dataset can provide the same logic as an existing system but via an alternative methodology that better aligns with Power BI or the specific project need.