Alberto Ferrari wrote an interesting article about a strange behavior of AutoExist in normalized data models. I always say that a star schema is the best practice in Power Pivot and Tabular data modeling. The issue described by Alberto is another good reason to avoid snowflake schemas.

I think that an example is better than many words. Consider this simple measure working in a star schema where all product attributes (such as Category and Subcategory) are in the same denormalized DimProduct table:

SalesOfBikes := CALCULATE ( [Sales], DimProduct[Category] = “Bikes” )

If you have a snowflake schema with DimProduct, DimProductSubcategory and DimProductCategory tables, you have to write a much longer and complex DAX formula in order to obtain the same result:

SalesOfBikes :=
CALCULATE (
    [Sales],
    DimProductCategory[EnglishProductCategoryName] = “Bikes”,
    CALCULATETABLE (
        DimProductCategory,
        FILTER (
            ALL ( DimProductSubcategory ),
            IF (
                ISFILTERED ( DimProductSubcategory[EnglishProductSubcategoryName] ),
                CONTAINS (
                    VALUES ( DimProductSubcategory ),
                    DimProductSubcategory[ProductSubcategoryKey], DimProductSubcategory[ProductSubcategoryKey]
                ),
                TRUE
            )
        )
    )
)

Which seems crazy, and actually it is…

The reasons are interesting and well described in the AutoExist and Normalization article on SQLBI.

CALCULATE
Context transition

Evaluates an expression in a context modified by filters.

CALCULATE ( <Expression> [, <Filter> [, <Filter> [, … ] ] ] )

CALCULATETABLE
Context transition

Evaluates a table expression in a context modified by filters.

CALCULATETABLE ( <Table> [, <Filter> [, <Filter> [, … ] ] ] )

FILTER

Returns a table that has been filtered.

FILTER ( <Table>, <FilterExpression> )

ALL
CALCULATE modifier

Returns all the rows in a table, or all the values in a column, ignoring any filters that might have been applied.

ALL ( [<TableNameOrColumnName>] [, <ColumnName> [, <ColumnName> [, … ] ] ] )

IF

Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE.

IF ( <LogicalTest>, <ResultIfTrue> [, <ResultIfFalse>] )

ISFILTERED

Returns true when there are direct filters on the specified column.

ISFILTERED ( <TableNameOrColumnName> )

CONTAINS

Returns TRUE if there exists at least one row where all columns have specified values.

CONTAINS ( <Table>, <ColumnName>, <Value> [, <ColumnName>, <Value> [, … ] ] )

VALUES

When a column name is given, returns a single column table of unique values. When a table name is given, returns a table with the same columns and all the rows of the table (including duplicates) with the additional blank row if present.

VALUES ( <TableNameOrColumnName> )