Data Modeling with Microsoft Power BI

Chapter 10. Building a Data Model with DAX

With DAX you are writing data analysis expressions, which allow you to create calculated tables, calculated columns, and, most importantly, measures (and row-level security and queries, which aren’t in the scope of this book). Everything you can achieve with calculated tables and columns, you can also achieve with solutions in Power Query/M and with SQL. If you just started with Power BI, then you need to learn DAX anyway; some problems can only be solved with measures written in DAX—and you might implement the transformations to build your data model in DAX as well.

Normalizing

As Chapters 1 and 2 detail, normalizing is important for fact tables and means that you strip the table of replicated information. You only keep foreign keys to one or more tables, which contain DISTINCT lists of the otherwise redundant information. These other tables are the dimension tables.

With that said, normalizing is as easy as removing all columns with repeated information that don’t comprise the (primary) key of the information and putting them into a table of their own. To find out which columns contain repeated information, I create a table visual in Power BI with a single column or a combination of columns that might have one-to-one relationships with each other.¹ Power BI will automatically show only the distinct values.

In Figure 10-1, the combinations of columns listed here are candidates for dimensions:

• Country

• Discount Band

• Product, Manufacturing ...