Pro DAX and Data Modeling in Power BI: Creating the Perfect Semantic Layer to Drive Your Dashboard Analytics

Table of Contents
About the Author
About the Technical Reviewer
Acknowledgments
Introduction
Chapter 1: Using Power BI Desktop to Create a Data Model
	Data Modeling in the Power BI Desktop Environment
		The Model Icon
		The Model Window
		The Model Home Ribbon
	Designing a Power BI Desktop Data Model
		Creating Relationships
		Managing Relationships
		Examining Relationships
		Deleting Relationships
		Deleting Multiple Relationships
		Editing Relationships
		Creating Relationships Automatically
	Inhibiting Relationship Autodetection
		Deactivating Relationships
		The Meaning Behind Table Relationships
		Cardinality in Relationships
		Cardinality Indicators
	Conclusion
Chapter 2: Extending the Data Model
	The Properties Pane
	Managing Power BI Desktop Data
		Manipulating Tables
			Renaming Tables
			Deleting a Table
		Manipulating Fields
			Renaming a Field
			Deleting Fields
			Moving Fields
	Power BI Data Types
	Formatting Power BI Desktop Data
	Preparing Data for Dashboards
		Categorize Data
		Apply a Default Summarization
		Define the Sort by Column
	Key Columns
	Nullable Fields
		The Power BI Desktop Data View
	The Table Tools Ribbon
	The Column Tools Ribbon
	Sorting Data in Power BI Desktop Tables
		Setting Field Widths
		Currency Formats
	Conclusion
Chapter 3: The Semantic Layer
	Data Model Topologies
	Field and Table Names
	Descriptions
		Hiding Tables
		Hiding Fields
	Display Folders and Subfolders for Measures
		Creating a Folder
		Removing Fields from a Folder
		Moving Fields Between Folders
		Removing a Folder
		SubFolders
	Items in Multiple Folders
	Tables to Contain Measures
	Hierarchies
		Creating a Hierarchy
		Removing Fields from a Hierarchy
		Extending a Hierarchy
		Removing a Hierarchy
	Binning and Grouping
		Grouping
		Binning
	Naming Conventions in the Semantic Model
	Custom Formats
	Data Model Aesthetics
		Collapsing and Expanding Tables
	Tabs in Data Modeling
	Conclusion
Chapter 4: Calculated Columns
	Types of Calculations
	Calculated Columns or Measures?
	Calculated Columns
		How to Add a Calculated Column
	Concatenating Column Contents
	Renaming Calculated Columns
	Tweaking Text
	Using Table Names in Calculated Columns
	Handling Mistakes
		Modifying a Calculated Column
	Simple Calculations
		Math Operators
		More Complex Math
		Rounding Values
	Cascading Column Calculations
		Applying a Specific Format to a Calculation
	Calculation Options
	Conclusion
Chapter 5: Calculating Across Tables
	Calculating Across Tables
		Counting Reference Elements
		Using RELATED() to Traverse a Simple Data Model
		Using RELATED() to Traverse a Complex Data Model
	Cross Filter Direction
	Modifying Cross Filter Direction
	Using Functions in New Columns
		Statistical Functions in Calculated Columns
	Summarizing for Each Row in a Table
	Limitations of Calculated Columns
	Conclusion
Chapter 6: DAX Logical Functions
	Simple Logic-the IF() Function
		Exception Indicators
		Explaining the IF() Function
		Creating Alerts
			Comparison Operators
		Testing the Absence of Data
		Nested IF() Functions
		Creating Custom Groups Using Multiple Nested IF() Statements
		Multiline Formulas
	Making Good Use of the Formula Bar
	Keyboard Shortcuts in the Formula Bar
		Complex Logic
			Logical Operators
			The SWITCH() Function
		Standard SWITCH()
		SWITCH() with TRUE()
	DAX Logical and Information Functions
		Formatting Logical Results
		Safe Division
	Testing for Blank or Empty Values
	Testing for Error Values
	Conclusion
Chapter 7: Date and Time Calculations in Columns
	Date Calculations
		Extracting Date Elements
	Date Elements
		Extracting Time Elements
	Date Calculations
		Setting Dates in a Calculation
			The NOW() and TODAY() Functions
			Setting a Specific Date
			The DATE() Function
			The DATEVALUE() Function
	Assembling Usable Dates
	Adding or Subtracting Dates
		Expressing the Difference Between Two Dates as Year and Month
		Extrapolating Dates
		Adding or Subtracting Months
		Calculating Years
		Adding Time to a Datetime
	Date and Time Formatting
	Conclusion
Chapter 8: Introduction to Measures
	A First Measure: Number of Cars Sold
	Basic Aggregations in Measures
	Default Measures
	Measures Are Column-Based Calculations
	Ways to Create Measures
	Modifying Measures
	Field References
		Composite Measures
		Cross-Table Measures
		Cascading Measures
		Implicit Filters Applied To Measures
		Naming Convention for Measures
		Annotate Measures
	Measure Recalculation
	Conclusion
Chapter 9: Filtering Measures
	Filtering Data in Measures
	Simple Filters
		Text Filters
		Numeric Filters
	Boolean (True/False) Filters
	Filtering Dates
		Time Filters
	More Complex Filters
		Multiple Criteria in Filters
	AND/OR Filters in Measures
		Complementary Choices (AND Filters)
		Alternative Choices (OR Filters)
		Alternative Elements from a Single Column
		Alternatives Across Columns
		Excluding Elements (NOT Filters)
		Partial Text Filtering
	NULL (Blank or Empty Cell) Handling
		Using Multiple Filters
	The Extent of Filtering in CALCULATE()
	Limits on CALCULATE() Filters
	Conclusion
Chapter 10: CALCULATE() Modifiers
	Calculating Percentages of Totals
		A Simple Percentage
		REMOVEFILTERS() or ALL()?
		Removing Multiple Filter Elements
	REMOVEFILTERS() Constraints
	Extending the Scope of REMOVEFILTERS()
		Defining a Series of Fields Not to Filter, One by One
		Specifying That All the Fields in a Table Will Not Be Filtered
		Removing Filtering from an Interconnected Set of Tables
		Specifying a Small Set of Fields in a Table That Will Remain Filtered While All the Other Fields in the Table Are Not Filtered
		Visual Totals
	Explicit Measure Filters and Modifiers Cannot Be Overridden
	KEEPFILTERS()
	Conclusion
Chapter 11: The Filter() Function
	Filter
	Displaying the Output from a FILTER()
	Filtering on Measures
	Filter Criteria Inside the FILTER() Function
		Multiple Filter Conditions When Filtering on a Single Table
			OR Filters
			AND Filters
			Strings
			Numbers
			Dates
			Blank
			Boolean
		Filtering on Criteria from Different Tables
	FILTER() Caveats
	Conclusion
Chapter 12: Iterators
	DAX Iterator Functions to Replace Calculated Columns
	Iterator Parameters
	Aggregator and Iterator Functions
	Iterators and the Data Model
	Iterator Functions or Calculated Columns?
	AVERAGEX(): The Ratio of the Sum vs. the Sum of the Ratio
	Filtering the Table Input for an Iterator
		Using FILTER() Inside an Iterator
		Using Moderator Functions Inside an Iterator
	Count Iterators
	Available Iterators
	Ranking Output
		Ranking Using Multiple Columns
		Handling Ties in RANKX()
	Percentile Calculations Using Iterators
	Conclusion
Chapter 13: Creating and Applying a Date Dimension
	Why Use a Date Dimension?
	Creating the Date Table
	Marking a Table as a Date Table
	Extending the Date Dimension
	Year Elements
	Quarter Elements
	Month Elements
	Week Elements
	Day Elements
	Date Elements
	Combination Elements
	Adding Sort by Columns to the Date Table
		Adding the Date Table to the Data Model
	Using a Date Table
		Alternative Date Table Generation Techniques
	Importing a Date Dimension
	Conclusion
Chapter 14: Time Intelligence
	Adding Time Intelligence to a Data Model
	Applying Time Intelligence
		YearToDate, QuarterToDate, and MonthToDate Calculations
		Fiscal Year Cumulative Calculations
	Comparisons with Previous Time Periods
		Looking at Data for the Same Time Point During the Previous Year
		Comparing with the Same Date Period from a Different Quarter, Month, or Year
		Comparing a Metric with the Result from the Previous Time Period
		Comparing Data over Any Time Period
	Specifying Ranges of Dates
		Analyze Data as a Ratio over Time
	Extending Core Time Intelligence Functions
		Comparing Data from Previous Years
	Rolling Aggregations over a Period of Time
	Which DAX Functions to Use for Comparison over Time?
	Defining Relative Time Periods in DAX
	Time Intelligence Without a Date Dimension
	Conclusion
Chapter 15: DAX Variables
	All About Variables
		Variable Usage
		Variable Names
		Creating Variables
		Using Variables
	Variable Output
	Basic Variable Use
	Variables and Intellisense
	Basic Variable Assignment
		Assigning Texts to Variables
		Assigning Calculations to Variables
		Assigning Tables to Variables
	What to RETURN
	Multiple Variables in a Measure
	Variable Reuse Inside a Measure
	Variables in Calculated Columns
	Filtering Using a Measure as the Comparison Element
	Commenting DAX
		Commenting Lines
		Commenting Blocks of Code
	Conclusion
Chapter 16: Table Functions
	Table Variables in Table Functions
	The SUMMARIZECOLUMNS Function
		Column List Without Filters or Aggregations
		Column List with Filters
		Column List with Aggregated Values
		Column List, Filter, and Aggregation
	Adding Columns to the Output from Table Functions
	Filtering Table Function Output
		Advanced Filtering Using CALCULATETABLE()
	Removing Columns
	SELECTCOLUMNS
		Simple SELECTCOLUMNS()
		SELECTCOLUMNS() Across Multiple Tables
		Filtering SELECTCOLUMNS()
	INTERSECT
	UNION
	EXCEPT
	CROSSJOIN()
	Table Functions
	Conclusion
Chapter 17: Beyond the Data Model
	Adding Data
	The DAX Table Constructor
		Table Constructor Structure
	DATATABLE()
	Many to Many Relationships in the Data Model
	Avoiding Many to Many Relationships
	LOOKUPVALUE()
		Avoid Hardcoded Values
		Imitate a Table Relationship
	Lookup an Element Between a Range of Values
	USERELATIONSHIP()
	CROSSFILTER ()
	Conclusion
Chapter 18: Evaluation Context
	Row Context
	Row Context Beyond Tables
	Filter Context
	Overriding the Initial Evaluation Context
	Overriding the Row Context
	Overriding the Implicit Filter Context
	Conclusion
Appendix: Sample Data
	Sample Data
		Downloading the Sample Data
Index