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