Data Modeling with Microsoft Power BI

Chapter 11. Real-World Examples Using DAX

DAX and the data model go hand-in-hand. You can solve some challenging use cases directly in the model, so there’s no need to write a single line of a DAX formula (this applies to most one-to-many relationships—but there are exceptions, as you will learn in this chapter). Other solutions involve a cooperation between the data model and DAX (like activating an inactive relationship). In rare cases, tables have no relationship defined at all in the data model, but you create a relationship with the TREATAS function in DAX, which will only exist during evaluation of the DAX expression.

This chapter covers the following use cases in DAX:

• How DAX can help you to implement binning based on a table that defines the ranges of each bucket. Binning is the idea of not showing the actual values but the bucket a value falls into (like small, medium, or large).

• I use a budget as an example of a data model with more than one fact table. Here, you need to overcome the challenge that the granularity of one of the fact tables might not be on the primary key level of a dimension table. This leads to a many-to-many cardinality between the fact and dimensions table. DAX can help here.

• No single button in Power BI Desktop creates multi-language reports; you need to work around several problems. I prefer a workaround that’s solely data-driven and doesn’t need any changes in the data model if new translations or new languages are added. To achieve this, ...