Companion content

Insert your email address and press Download for access to the files used in this book.
9781509306978.zip(1.4GB)

Keep me informed about BI news and upcoming articles with a bi-weekly newsletter (uncheck if you prefer to proceed without signing up for the newsletter)

Send me SQLBI promotions (only 1 or 2 emails per year)

By pressing the Download button you are agreeing to our Privacy Policy.

Errata corrige

To ensure the ongoing accuracy of this book and its companion content, we have reviewed and confirmed the errors listed below. If you find a new oversight, please report it to us.

  • Page 8: Replace understood with understand

    In the section, “DAX requires theory” paragraph 2, lines 4-5 the verb in the sentence “The problem is not DAX but the fact that you do not yet understood exactly how DAX works.” is incorrect. The sentence should read, “The problem is not DAX but the fact that you do not yet understand exactly how DAX works.”

    Edited on Dec 1, 2019
  • Page 52: Missing MonthNumber column in Figure 2-10

    Figure 2-10 should show a column named “MonthNumber” and the content in each visible row should be 1.

    Edited on Sep 27, 2019
  • Page 73: Replace Color with Brand

    In the last paragraph of the page, the first sentence references “Color” instead of “Brand”. The right sentence is “The Brand Name measure uses COUNTROWS to check whether the Brand column of the Products table only has one value selected.”

    Edited on Jan 3, 2020
  • Page 82: Inverted reference to the Total column and row

    In the first paragraph after Figure 4-3 the sentence, “In the Total row, the filter is only on the brand, whereas in the Total column the filter is only on the year.” should be the other way around.
    The right sentence reads, “In the Total row, the filter is only on the year, whereas in the Total column the filter is only on the brand.”.

    Edited on Aug 29, 2019
  • Page 89: Replace Unit Cost with Net Price for GrossMargin% calculation

    The code snippet must have Net Price instead of Unit Cost in the denominator of the GrossMargin% calculation. This does not affect the explanation of the section, but it is the right calculation of Margin.

    GrossMargin% := ( Sales[Net Price] - Sales[Unit Cost] ) / Sales[Net Price]
    
    Edited on Sep 27, 2019
  • Page 91: Missing comma after RELATEDTABLE ( Sales )

    The sample code does not have a comma after line 6 containing RELATEDTABLE ( Sales ). The right code is the following:

    SUMX (
        'Product Category',                   -- Scans the Product Category table
        SUMX (                                -- For each category
            RELATEDTABLE ( 'Product' ),       -- Scans the category products
            SUMX (                            -- For each product
                RELATEDTABLE ( Sales ),       -- Scans the sales of that product
                Sales[Quantity]               --
                    * 'Product'[Unit Price]   -- Computes the sales amount of that sale
                    * 'Product Category'[Discount]
            )
        )
    )
    
    Edited on Sep 11, 2019
  • Page 157: Missing parenthesis after CALCULATE

    In the last code snippet an opening parenthesis is missing after CALCULATE .
    The right code is the following:

    'Product'[Product Sales] =
    CALCULATE (
        SUMX (
            Sales,
            Sales[Quantity] * Sales[Net Price]
        )
    )
    
    Edited on Feb 22, 2020
  • Page 157: Small change in last paragraph

    Change the first sentence in the last paragraph from:

    The Product Sales column correctly computes the sum of Sales Amount only for the current product in the Product table.

    To:

    The Product Sales column computes the sum of Sales Amount for only the current product in the Product table.

    Edited on Apr 3, 2020
  • Page 161: Replace Unit Cost with Net Price in margin calculation

    The second code in the page should reference Net Price instead of Unit Cost in the Margin and MarginPct calculation. This does not affect the explanation of the page for the circular dependency, but here is the right calculation of Margin:

    Sales[MarginPct] = DIVIDE ( Sales[Margin], Sales[Net Price] )
    Sales[Margin] = Sales[MarginPct] * Sales[Net Price]
    
    Edited on Sep 27, 2019
  • Page 167: Wrong definition of the Delivered Amount 2007 v2 measure

    The code for the Delivered Amount 2007 v2 measure in the first code sample makes incorrect use of USERELATIONSHIP.
    The code should be the following – the only difference is on the first line:

    Delivered Amount 2007 v2 = 
    CALCULATE (
        CALCULATE (
            [Sales Amount],
            FILTER (
                Sales,
                RELATED ( 'Date'[Calendar Year] ) = "CY 2007"
            )
        ),
        USERELATIONSHIP (
            Sales[Delivery Date],
            'Date'[Date]
        )
    )
    Edited on Nov 9, 2019
  • Page 176: Replace TotalCost with SalesAmount in the denominator of the Margin% calculation

    Use SalesAmount instead of TotalCost at line 9 of the second code block. The right code is the following:

    Margin% :=
    VAR SalesAmount =
        SUMX ( Sales, Sales[Quantity] * Sales[Net Price] )
    VAR TotalCost =
        SUMX ( Sales, Sales[Quantity] * Sales[Unit Cost] )
    VAR Margin =
        SalesAmount - TotalCost
    VAR MarginPerc =
        DIVIDE ( Margin, SalesAmount )
    RETURN
        MarginPerc
    

    Use Sales[Net Price] instead of Sales[Unit Cost] at line 12 of the last code block. The right code is the following:

    Margin% :=
        DIVIDE (
            SUMX (
                Sales,
                Sales[Quantity] * Sales[Net Price]
            ) - SUMX (
                    Sales,
                    Sales[Quantity] * Sales[Unit Cost]
                ),
            SUMX (
                Sales,
                Sales[Quantity] * Sales[Net Price]
            )
        )
    
    Edited on Mar 25, 2020
  • Page 192: Missing semicolon (:) for measure definition

    The code at page 192 defines a measure. Therefore, the assignment symbols must be := instead of =.
    The code should be the following (the only difference is on the first line):

    Date of Max :=
    VAR MaxDailySales = [Max Daily Sales]
    VAR DatesWithMax =
        FILTER (
            VALUES ( 'Date'[Date] ),
            [Sales Amount] = MaxDailySales
        )
    VAR Result =
        IF (
            COUNTROWS ( DatesWithMax ) = 1,
            DatesWithMax,
            BLANK ()
        )
    RETURN
        Result
    
    Edited on Oct 7, 2019
  • Page 196: Inverted Sales Amount and Products columns

    Figure 7-6 on page 197 shows the Sales Amount and Products columns as inverted, compared to the order defined in the code shown on page 196.
    In order to get the result shown in Figure 7-6 on page 197, the code on page 196 should be:

    Colors =
    ADDCOLUMNS (
        VALUES ( 'Product'[Color] ),
        "Sales Amount", [Sales Amount],
        "Products", CALCULATE ( COUNTROWS ( 'Product' ) )
    )
    
    Edited on Oct 7, 2019
  • Page 197: Inverted Sales Amount and Products columns

    Figure 7-6 on page 197 shows the Sales Amount and Products columns as inverted compared to the order defined in the code shown on pages 196 and 197.
    The code on page 197 should be:

    Colors =
    SELECTCOLUMNS (
        VALUES ( 'Product'[Color] ),
        "Color", 'Product'[Color],
        "Sales Amount", [Sales Amount],
        "Products", CALCULATE ( COUNTROWS ( 'Product' ) )
    )
    
    Edited on Oct 8, 2019
  • Page 207: Typo in ‘Sales Ranking'[Sale] column reference

    The first line contains a typo in the column reference that should be ‘Sales Ranking'[Sales] instead of ‘Sales Ranking'[Sale].

    Edited on Mar 30, 2020
  • Page 240: Wrong measure name (YTD should be used instead of PY)

    The first sample code uses the wrong prefix; it should be YTD instead of PY.
    The correct code is the following:

    YTD Sales :=
    CALCULATE (
        [Sales Amount],
        DATESYTD ( 'Date'[Date] )
    )
    -- is equivalent to
    YTD Sales :=
    CALCULATE (
        [Sales Amount],
        DATESYTD ( CALCULATETABLE ( DISTINCT ( 'Date'[Date] ) ) )
    )
    
    Edited on Oct 11, 2019
  • Page 262: Translation of DATESYTD using the second argument

    The corresponding FILTER of DATESYTD described in the third code sample of the page should be the following:

    VAR LastInSelection = MAX ( 'Date'[Date] )
    VAR SelectionEndOfYear = 
        DATE ( YEAR ( LastInSelection ), <month>, <day> )
    VAR DifferentYear = ( SelectionEndOfYear >= LastInSelection )
    VAR LastDayPreviousYear =
        DATE ( YEAR ( LastInSelection ) - DifferentYear, <month>, <day> ) 
    RETURN
        FILTER ( 
            ALL ( 'Date'[Date] ), 
            'Date'[Date] > LastDayPreviousYear 
                && 'Date'[Date] <= LastInSelection 
        )
    
    Edited on Oct 22, 2019
  • Page 322: FILTERS instead of FILTER

    In section “Understanding differences between VALUES and FILTERS” at paragraph 2 line 4 the sentence “VALUES returns two colors, whereas FILTER returns all the filtered four.” inaccurately references FILTER instead of FILTERS. The right sentence is: “VALUES returns two colors, whereas FILTERS returns all the filtered four.”

    Edited on Sep 10, 2019
  • Page 330: Small typo in Figure 10-16 description

    The name of the first column reference in the description of Figure 10-16 should be “GlobalPct” instead of “GlocalPct”.

    Edited on Oct 22, 2019
  • Page 331: Wrong column reference in code

    Line 4 of the second code block in the page should reference Customer[CustomerKey] instead of Sales[CustomerKey].
    The right code is the following:

    NonBuyingCustomers :=
    VAR SelectedCustomers =
        CALCULATETABLE (
            DISTINCT ( Customer[CustomerKey] ),
            ALLSELECTED ()
        )
    VAR CustomersWithoutSales =
        FILTER (
            SelectedCustomers,
            ISEMPTY ( RELATEDTABLE ( Sales ) )
        )
    VAR Result =
        COUNTROWS ( CustomersWithoutSales )
    RETURN
        Result
    
    Edited on Nov 2, 2019
  • Page 407: Wrong figure reference

    The paragraph after Figure 13-12 inaccurately references another figure; the final sentence of that paragraph should read:

    the result is similar to Figure 13-12, but with smaller amounts:

    Edited on Oct 31, 2019
  • Page 422: Remove comma from last argument of SUMMARIZE

    The line 7 of the last code sample must not have the final comma. The right code is the following:

    EVALUATE
    ADDCOLUMNS (
        SUMMARIZE (
            Sales,
            'Date'[Calendar Year],
            'Date'[Month],
            'Date'[Month Number]
        ),
        "Amt", AVERAGEX (
            RELATEDTABLE ( Sales ),
            Sales[Quantity] * Sales[Net Price]
        )
    )
    ORDER BY
        'Date'[Calendar Year],
        'Date'[Month Number]
    
    Edited on Jul 3, 2020
  • Page 451: Missing comma in measure declaration

    A colon is missing in the first line of the last code block on the page. The right code is the following:

    Delivered Amount :=
    CALCULATE (
        [Sales Amount],
        CALCULATETABLE (
            Sales,
            USERELATIONSHIP ( Sales[Delivery Date], 'Date'[Date] )
        )
    )
    
    Edited on Jul 6, 2020
  • Page 467: Remove square brackets from temporary column name / 1

    Line 13 of the sample code should not have square brackets in the name definition.
    The right code is the following:

    DEFINE
        MEASURE Sales[Sales Amount] =
            SUMX ( Sales, Sales[Quantity] * Sales[Net Price] )
    EVALUATE
    VAR NonBlueColors =
        FILTER (
            ALL ( 'Product'[Color] ),
            'Product'[Color] <> "Blue"
        )
    VAR AddC2 =
        ADDCOLUMNS (
            NonBlueColors,
            "C2", 'Product'[Color]
        )
    VAR SelectOnlyC2 =
        SELECTCOLUMNS ( AddC2, "C2", [C2] )
    VAR Result =
        ADDCOLUMNS ( SelectOnlyC2, "Sales Amount", [Sales Amount] )
    RETURN Result
    ORDER BY [C2]
    
    Edited on Sep 25, 2019
  • Page 468: Remove square brackets from temporary column name / 2

    Line 13 of the sample code should not have square brackets in the name definition.
    The right code is the following:

    DEFINE
        MEASURE Sales[Sales Amount] =
            SUMX ( Sales, Sales[Quantity] * Sales[Net Price] )
    EVALUATE
    VAR NonBlueColors =
        FILTER (
            ALL ( 'Product'[Color] ),
            'Product'[Color] <> "Blue"
        )
    VAR AddC2 =
        ADDCOLUMNS (
            NonBlueColors,
            "C2", 'Product'[Color]
        )
    VAR SelectOnlyC2 =
        SELECTCOLUMNS ( AddC2, "C2", [C2] )
    VAR TreatAsColor =
        TREATAS ( SelectOnlyC2, 'Product'[Color] )
    VAR Result =
        ADDCOLUMNS ( TreatAsColor, "Sales Amount", [Sales Amount] )
    RETURN Result
    ORDER BY 'Product'[Color]
    
    Edited on Sep 25, 2019
  • Page 491: Replace Brand with Sales

    In the last paragraph, replace the “Brand” table name with “Sales” in the first sentence. Instead of “The reason for this is that the filter context on Product[Brand] affects Sales because of the one-to-many relationship between Product and Brand.” the correct sentence is “The reason for this is that the filter context on Product[Brand] affects Sales because of the one-to-many relationship between Product and Sales.”

    Edited on Jun 27, 2020
  • Page 503: Fix figure reference in first paragraph

    In the first paragraph, the reference to Figure 15-21 must be changed to Figure 15-22. The right sentence should be “This does require using the columns from the CountryRegions and Brands tables in the report, which will appear as in Figure 15-21.”

    Edited on Jun 27, 2020
  • Page 516: Changed paragraph after Figure 15-37

    The first paragraph of this page describes a different version of the model described in Figure 15-37 and it should be replaced by the following paragraph:

    Focus on the Date table. Date filters Sales through the only active relationship (Date[Date] to Sales[Date]). There are two relationships between Date and Sales. One of them is inactive to avoid ambiguity. There is also a relationship between Date and Customer, based on Customer[FirstSale] that must be inactive. If this latter relationship were activated, then the filter from Date could reach Sales following two paths, making the model ambiguous. Thus, this model works just fine because it only uses the active relationships.

    Edited on Nov 18, 2019
  • Page 551: Wrong separator in schematic representation of physical memory layout

    Wrong row separator (“,” instead of “|”) before the last row in the schematic representation of the physical memory layout of a row store.
    The correct representation is the following:

    ID,Name,Color,Unit Price|1,Camcorder,Red,112.25|2,Camera,Red,97.50|3,Smartphone,
    White,100.00|4,Console,Black,112.25|5,TV,Blue,1,240.85|6,CD,Red,39.99|7,
    Touch screen,Blue,45.12|8,PDA,Black,120.25|9,Keyboard,Black,120.50
    
    Edited on Nov 19, 2019
  • Page 594: Change “truncate” to “round” in second code block

    Change “truncate” to “round” in the comments of the second code block on the page.
    The right comment is:

    -- Round to 5 minutes
    --   change 5 to 15 to round to 15 minutes
    --   change 5 to 60 to round to the hour
    
    Edited on Jun 27, 2020
  • Page 677: Correction to description of Figure 20-21

    The description of Figure 20-21 must finish using “by brand” instead of “by country”. The correct complete description is:
    FIGURE 20-21 Query Plan pane running the query for the Cashback (slow) measure reported by brand.

    Edited on Jun 27, 2020
  • Page 679: Correction to description of Figure 20-23

    The description of Figure 20-23 must finish using “by customer” instead of “by brand”. The correct complete description is:
    FIGURE 20-23 Fam. Sales reported by customer.

    Edited on Jun 27, 2020
  • Page 681: Correction to description of Figure 20-25

    The description of Figure 20-25 must finish start with “Query Plan” instead of “Server Timings”. The correct complete description is:
    FIGURE 20-25 Query Plan running the query for the Fam. Sales (slow) measure reported by customer.

    Edited on Jun 27, 2020
  • Page 690: Change column name from unit Price to Net Price

    In the second paragraph change the column reference from Unit Price to Net Price. The right sentence is “For example, consider the following query where the Rounded Sales measure computes its result
    rounding Net Price to the nearest integer.”

    Edited on Jun 27, 2020