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 agree 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 52: Missing MonthNumber column in Figure 2-10

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

    Edited on Sep 27, 2019
  • Page 82: Inverted reference to 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 inverted.
    The right sentence is “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 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 a correct calculation of the Margin.

    GrossMargin% := ( Sales[Net Price] - Sales[Unit Cost] ) / Sales[Net Price]
    
    Edited on Sep 27, 2019
  • Page 91: Missing comma after RELATEDATABLE ( 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 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 it is a correct calculation of the 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 Delivered Amount 2007 v2 measure

    The code for the Delivered Amount 2007 v2 measure in the first code sample has a wrong 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 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

    The figure 7-6 at page 197 has the columns Sales Amount and Products inverted compared to the order defined in the code shown at page 196.
    In order to get the result shown in Figure 7-6 at page 197, the code at 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

    The figure 7-6 at page 197 has the columns Sales Amount and Products inverted compared to the order defined in the code shown at page 196 and 197.
    The code at 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 240: Wrong measure name (YTD should be used instead of PY)

    The first sample code has a wrong prefix, it should be YTD instead of PY.
    The correct code should be 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.” wrongly 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 type in Figure 10-16 description

    The name of the first column reference in the description of Figure 10-16 is “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 wrongly references another figure, the right final sentence of that paragraph should be:

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

    Edited on Oct 31, 2019
  • 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 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 one:

    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