1

I have a dimension attribute which has either 1 or 0 and few measures which are count, sum or average. I don't want to display values of measures which are related to attribute 0. Ex:

  • Attribute Name: Is_related

  • List item : Values: 0 or 1

    now, there are few measures like count_of_family which has total value of 1000 in which 700 are related to Is_Related 1 and 300 are related to Is_Related to 0.

I have made Is_Related member value (0) disabled but it is not applied on measure. So, for users they are getting 1000 as by default value and when they are selecting 1 it is filtering it down to 700. I want count_of_family measure to display 700 by default (i.e. omitting data related to 0).

Community
  • 1
  • 1
Zerotoinfinity
  • 6,290
  • 32
  • 130
  • 206

1 Answers1

2

Easiest way to do this is to make (1) the DefaultMember of the Is_related attribute hierarchy in the dimension.

Using Excel Pivot Tables as an example of what the result is in the front end:

  • Whenever the user creates a new Pivot Table, only the sub-cube (Dimension.Is_related.[1],OtherDimension.All,OtherDimension2.All.... etc) is shown
  • But if the user drags the Is_related hierarchy onto the pivot table (most appropriately - to the Filters section), only (1) will be selected. They can then select All (or 0), if they want to.

Downside of non-ALL default members is that you have to remember that you set the default member. Any MDX query will only query the subcube (excluding Is_related.[0] in your case), unless you specifically include Is_related.ALL in the WHERE. Which has caught me out in the past, wondering why my results are weird.

SebTHU
  • 1,385
  • 2
  • 11
  • 22
  • I implemented the same and also made isAgretable to false. Now, when I am browsing it from from cube it is showing me 1 only but when I am browsing it via excel power pivot, I am getting both Unknown and 1. – Zerotoinfinity Aug 23 '17 at 13:35
  • I have made Is_Related default to 1. After that, I have created Hierarchy. Is there any way to set default of hierarchy ? – Zerotoinfinity Aug 23 '17 at 14:23
  • It needs to be IsAggregatable True, AttributeHierarchyEnabled True. The latter will automatically create an attribute hierarchy (not visible in design UI) with levels ALL and [leaf]. – SebTHU Aug 25 '17 at 09:52