The Power BI model
Most of this chapter is based on a small sample model:
Figure 2.4.1: Diagram of the Power BI model
We have a fact table, fSales, with sales transactions for customers in the Customer table. A Customer has a responsible, who is an employee; the model therefore relates the Responsible column in Customer to the EmpNr column in Employee. So, with this model, we can report sales by customer, but also by employee, or any of either's attributes.
This model file, 2.4 AutoExist.pbix, can be found at https://github.com/PacktPublishing/Extreme-DAX/tree/main/Chapter2.4.
We will mainly work with the results of a single DAX measure for sales:
Sales = SUM(fSales[SalesPrice])
In addition, we use a measure that only returns sales for some product categories:
Sales (core products) =
CALCULATE(
[Sales],
KEEPFILTERS(
Product[Category]
IN {"Bikes", "Clothing", "Accessories"}
...