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 :=
    DimProductCategory[EnglishProductCategoryName] = “Bikes”,
        FILTER (
            ALL ( DimProductSubcategory ),
            IF (
                ISFILTERED ( DimProductSubcategory[EnglishProductSubcategoryName] ),
                CONTAINS (
                    VALUES ( DimProductSubcategory ),
                    DimProductSubcategory[ProductSubcategoryKey], DimProductSubcategory[ProductSubcategoryKey]

Which seems crazy, and actually it is…

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

Context transition

Evaluates an expression in a context modified by filters.

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

Context transition

Evaluates a table expression in a context modified by filters.

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


Returns a table that has been filtered.

FILTER ( <Table>, <FilterExpression> )

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> [, … ] ] ] )


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

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


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

ISFILTERED ( <TableNameOrColumnName> )


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

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


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 caused by an invalid relationship if present.

VALUES ( <TableNameOrColumnName> )