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.
Evaluates an expression in a context modified by filters.
CALCULATE ( <Expression> [, <Filter> [, <Filter> [, … ] ] ] )
Evaluates a table expression in a context modified by filters.
CALCULATETABLE ( <Table> [, <Filter> [, <Filter> [, … ] ] ] )
Returns a table that has been filtered.
FILTER ( <Table>, <FilterExpression> )
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> )