Search code examples
ssasssas-2016

SSAS OLAP yes/no Dimension including all rows on yes option

I need to add a new dimension to my OLAP cube to filter some rows.

Essentially if the users select "yes" it should appear all rows. If the users select "no" it should appear the rows identified.

I have a column in my factual table with 0 and 1 (no/yes)..

My problem is that I need to "ignore" this column if the users select yes and only look for it if the select no..

I thought on a Junk dimension but for this I need to have two attributes.

Any suggestions

Thanks

Solution

  • By "filtering some rows in cube" you mean apply filter if [No] member is selected in your dimension, correct? Otherwise show all measures like there is no filter.

    Can you use SCOPE redirection to [All] member when [Yes] is selected?

    SCOPE([YourDimension].[Yes]);
THIS=[YourDimension].[All];
END SCOPE;

    This should work for every measure that you have in cube.

    Moreover, even if cube ALTER is not allowed, you can achieve it in two steps way:

    1. Create measure for ALL (unfiltered) value.
    2. Show it only for certain members.

    E.g. [CY 2013] = your [Yes]:

    with
member [2013 is ALL] as ([Measures].[Order Count],[Date].[Calendar Year].[All])

member [2013] as
 iif([Date].[Calendar Year].CurrentMember is [Date].[Calendar Year].&[2013]
  or [Date].[Calendar Year].CurrentMember is [Date].[Calendar Year].[All Periods]
  ,[2013 is ALL],[Measures].[Order Count])

select {[Measures].[Order Count],[2013 is ALL],[2013]} on 0
,[Date].[Calendar Year].members on 1
from [Adventure Works]

    ALL_for_member

    But please try SCOPE as a first. Because it can be applied to any measure, even not created yet.