- •Contents at a glance
- •Contents
- •Introduction
- •Who this book is for
- •Assumptions about you
- •Organization of this book
- •Conventions
- •About the companion content
- •Acknowledgments
- •Errata and book support
- •We want to hear from you
- •Stay in touch
- •Chapter 1. Introduction to data modeling
- •Working with a single table
- •Introducing the data model
- •Introducing star schemas
- •Understanding the importance of naming objects
- •Conclusions
- •Chapter 2. Using header/detail tables
- •Introducing header/detail
- •Aggregating values from the header
- •Flattening header/detail
- •Conclusions
- •Chapter 3. Using multiple fact tables
- •Using denormalized fact tables
- •Filtering across dimensions
- •Understanding model ambiguity
- •Using orders and invoices
- •Calculating the total invoiced for the customer
- •Calculating the number of invoices that include the given order of the given customer
- •Calculating the amount of the order, if invoiced
- •Conclusions
- •Chapter 4. Working with date and time
- •Creating a date dimension
- •Understanding automatic time dimensions
- •Automatic time grouping in Excel
- •Automatic time grouping in Power BI Desktop
- •Using multiple date dimensions
- •Handling date and time
- •Time-intelligence calculations
- •Handling fiscal calendars
- •Computing with working days
- •Working days in a single country or region
- •Working with multiple countries or regions
- •Handling special periods of the year
- •Using non-overlapping periods
- •Periods relative to today
- •Using overlapping periods
- •Working with weekly calendars
- •Conclusions
- •Chapter 5. Tracking historical attributes
- •Introducing slowly changing dimensions
- •Using slowly changing dimensions
- •Loading slowly changing dimensions
- •Fixing granularity in the dimension
- •Fixing granularity in the fact table
- •Rapidly changing dimensions
- •Choosing the right modeling technique
- •Conclusions
- •Chapter 6. Using snapshots
- •Using data that you cannot aggregate over time
- •Aggregating snapshots
- •Understanding derived snapshots
- •Understanding the transition matrix
- •Conclusions
- •Chapter 7. Analyzing date and time intervals
- •Introduction to temporal data
- •Aggregating with simple intervals
- •Intervals crossing dates
- •Modeling working shifts and time shifting
- •Analyzing active events
- •Mixing different durations
- •Conclusions
- •Chapter 8. Many-to-many relationships
- •Introducing many-to-many relationships
- •Understanding the bidirectional pattern
- •Understanding non-additivity
- •Cascading many-to-many
- •Temporal many-to-many
- •Reallocating factors and percentages
- •Materializing many-to-many
- •Using the fact tables as a bridge
- •Performance considerations
- •Conclusions
- •Chapter 9. Working with different granularity
- •Introduction to granularity
- •Relationships at different granularity
- •Analyzing budget data
- •Using DAX code to move filters
- •Filtering through relationships
- •Hiding values at the wrong granularity
- •Allocating values at a higher granularity
- •Conclusions
- •Chapter 10. Segmentation data models
- •Computing multiple-column relationships
- •Computing static segmentation
- •Using dynamic segmentation
- •Understanding the power of calculated columns: ABC analysis
- •Conclusions
- •Chapter 11. Working with multiple currencies
- •Understanding different scenarios
- •Multiple source currencies, single reporting currency
- •Single source currency, multiple reporting currencies
- •Multiple source currencies, multiple reporting currencies
- •Conclusions
- •Appendix A. Data modeling 101
- •Tables
- •Data types
- •Relationships
- •Filtering and cross-filtering
- •Different types of models
- •Star schema
- •Snowflake schema
- •Models with bridge tables
- •Measures and additivity
- •Additive measures
- •Non-additive measures
- •Semi-additive measures
- •Index
- •Code Snippets
RETURN
IF ( ISBLANK ( RelPeriod ), "Future", RelPeriod
The two columns (RelPeriodCode and RelPeriod) are shown in the Date table in Figure 4-36.
FIGURE 4-36 The last two columns are computed using the formulas described in the previous paragraphs.
Being calculated columns, they are recomputed at every refresh of the data model. In this way, they change the tags assigned to the dates. You do not need to update the report, as it will always show the last processed day as today, the day before as yesterday, and so on.
Using overlapping periods
The techniques you have seen in the previous sections work fine, but they all have one strong limitation: The time periods cannot overlap. In fact, because you store the attribute that defines the time period in a calculated column, there can only be one value assigned to the column.
There are, however, scenarios where this is not possible. Suppose, for example, that you put some product categories on sale in different periods of the year. It is totally possible that, in the same period, more than one product category is on sale. At the same time, the same category might be on sale during multiple different time periods. Thus, in such a scenario, you cannot store the period of sales in the Products table or in the Date table.
A scenario in which you have many rows (categories) that need to be in
relationships with many rows (dates) is known as a many-to-many model. Many- to-many models are not easy to manage, but they provide extremely useful analyses and are worth describing. You will find a much more complete discussion on many-to-many models in Chapter 8, “Many-to-many relationships.” In this section, we only want to show that when many-to-many relationships are involved, the code tends to be harder to write.
The Discounts configuration table from this example is shown in Figure 4-37.
FIGURE 4-37 Different sales periods for different categories are stored in the Discounts configuration table.
Looking at the Discounts configuration table, you can see that in the first week of January 2007 and 2008, there are multiple categories on sale (computers and audio). The same applies to the first two weeks of August (audio and cell phones). In such a scenario, you can no longer rely on relationships, and you need to write DAX code that takes the current filter from the sale period and merges it with the already existing filter in Sales. This is accomplished by the following formula:
Click here to view code image
SalesInPeriod := SUMX (
Discounts,
CALCULATE (
[Sales Amount], INTERSECT (
VALUES ( 'Date'[Date] ),
DATESBETWEEN ( 'Date'[Date], Discounts[D
), INTERSECT (
VALUES ( 'Product'[Category] ), CALCULATETABLE ( VALUES ( Discounts[Cate