Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
IgnoreUnrelatedDimensions setting for PowerPivot

Answered IgnoreUnrelatedDimensions setting for PowerPivot

  • Friday, March 30, 2012 1:34 PM
     
     
    Sign In to Vote
    0

    Hi,

    Does PowerPivot have a setting similar to IgnoreUnrelatedDimensions in SSAS? My PowerPivot solution contains two measure groups with similar granularity except that one contains an additional dimension not relative to the other. When measures from the two are combined and the additional dimension included then the data becomes fuzzy due to unrelated dimensions being displayed.

    Below is an example that uses both PowerPivot and SSAS. You will notice that SSAS displays correctly but PowerPivot not.

    PowerPivotPlay.xlsx

     

All Replies

  • Monday, April 02, 2012 9:20 AM
    Moderator
     
     Answered
    Sign In to Vote
    0

    Hi Orion Pax,

    It is true in Analysis Services(UDM), the IgnoreUnrelatedDimensions property can help you achieve this behavior. In PowerPivot, there is no such kind of property to achieve this, in order to do that in PowerPivot, you can create another measure called IsValid that will return TRUE if the current selection in the PivotTable has a valid correspondent definition, otherwise FALSE. Finally, you can create your original measure that shows the value only when IsValid returns TRUE, otherwise return BLANK(), you can refer to MARCO RUSSO‘s log for detail information about this topic  http://www.sqlbi.com/articles/budget-and-other-data-at-different-granularities-in-powerpivot/ 

    Thanks,



    Challen Fu

    TechNet Community Support