Data Modeling with Microsoft Power BI

Chapter 9. Understanding a Data Model from the DAX Point of View

When it comes to Power BI, not all data modeling challenges can be solved in Power Query or the data source. Some data modeling solutions can only be implemented inside a DAX measure. I will point out such solutions in detail where appropriate. It’s also important to understand that the data model and DAX work hand-in-hand. This means that in some situations, you can exchange complexity in the Model view for complexity in a DAX measure, or the other way around. You have already seen such situations in Chapter 7. I will point out such situations in this and the following three chapters as well.

But before I dive into the complex examples, I want you to understand how DAX “sees” the data model.

Data Model

The relationship between DAX and the data model is twofold. You can use DAX to add calculated columns to a table and even create whole tables. In addition, DAX uses the information provided by the data model (read: filter relationships) to navigate through the model and access the data needed for the calculation.

To be more precise: whenever you write a formula (or a query) in DAX, it’s passed forward to the formula engine, which takes the appropriate steps (requesting values from the storage engine and doing its own calculations in case the storage engine cannot execute the calculations due to complexity). Both the formula and storage engines use all the information available from the data model to do their jobs. ...