Power View Automatic Metadata Filtering

Similar to Report Builder models (now obsolete), Power View (formerly known as Crescent) filters metadata automatically. The reasoning behind this feature is that the tool should guide the user to choose only tables that are related to the ones used on the report to avoid Cartesian joins. For example, if the user has selected Sales Amount from the Reseller Sales table, Power Pivot automatically disables Currency and Geography tables because they are not related to the Reseller Sales table. The Metadata pane shows these tables greyed out and the user cannot choose a column from these tables.

As useful as this feature is, it can get in the way for the same reasons the Report Builder metadata filtering didn’t work so well with multidimensional cubes. Marco Russo has already reported an issue that this feature ignores M2M calculations in the model. I believe this will be even a bigger issue one day when Power View supports multidimensional cubes because there are scenarios where dimensions are not directly related to a fact table but still can be used for slicing, such as a shim dimension for time calculations that is populated with scope assignments.

If you agree, please vote for the suggestion I’ve submitted on connect. Ideally, there should be a visual indicator that a table is not related but the user should still be able to select a column. Or, the modeler should be able to overwrite this behavior per table.