Clever Geek Handbook

MDX Exclude Results When Value Exists in Hierarchy

Please excuse me if I am wrong in terminology, I am relatively new to MDX / OLAP.

I have a dimension to define ethical walls. The size looks like this:

Ethical Wall (dimension)
   --> Matter ID (hierarchy)
   --> Walled (hierarchy)
   --> White Listed Initials (hierarchy)
   --> Black Listed Initials (hierarchy)

The hierarchy [Walled]

contains either true or false, depending on whether a wall was specified.

The "White" and "Black" list hierarchies contain the initials of users who either have or do not have access to matter, respectively. Note that the case is either whitelisted or blacklisted, it is never a combination of the two.

I was able to easily identify a script without a script and a whitelist, but I have a lot of problems with the blacklist. This is the where clause I have come up with so far:

({
    (
        [Ethical Wall].[Walled].&[True]
        ,[Ethical Wall].[White Listed Initials].&[XXX]
        ,[Ethical Wall].[Black Listed Initials].&[]
    )
    ,(
        [Ethical Wall].[Walled].&[True]
        ,[Ethical Wall].[White Listed Initials].&[]
        ,-{[Ethical Wall].[Black Listed Initials].&[XXX]}
    )
    ,(
        [Ethical Wall].[Walled].&[False]
        ,[Ethical Wall].[White Listed Initials].&[]
        ,[Ethical Wall].[Black Listed Initials].&[]
    )
})

Dropping it and presenting it in a table, the original dataset I'm filtering from looks something like this:

enter image description here

I only want to select Ids

which the user with the initials XXX has access to . By applying the filter on top, I return all 3 Ids

. The result set I'm looking for is just Id

1

and 2

. The filter on top corresponds to the following:

enter image description here

I understand why my filter is extracting all 3 Ids

, but I do not understand how to edit the middle of the filter to exclude correctly Ids

. This is the offensive part of the filter:

    ,(
        [Ethical Wall].[Walled].&[True]
        ,[Ethical Wall].[White Listed Initials].&[]
        ,-{[Ethical Wall].[Black Listed Initials].&[XXX]}
    )

How can I revise my filter to fit this dataset as follows?

enter image description here

+3


source to share


2 answers


We found a solution !!

Using kits:

SET notwalled AS exists( 
       selectedmatters, 
       { 
         [Ethical Wall].[Walled].&[False] 
       } 
     ) 
  SET whitelisted AS exists( 
       selectedmatters, 
       { 
         [Ethical Wall].[White Listed Initials].&[XXX] 
       } 
     ) 
  SET blacklisted AS EXCEPT( 
       selectedmatters, 
       exists( 
         selectedmatters, 
         { 
           [Ethical Wall].[Black Listed Initials].&[XXX], 
           [Ethical Wall].[Black Listed Initials].&[] 
         } 
       ) 
     )


And then the concatenation:

UNION(notwalled, whitelisted, blacklisted)

I don't cry for me anymore.

0


source


Why can't you simplify this?



WHERE
({
    (
        [Ethical Wall].[Walled].&[True]
        ,[Ethical Wall].[White Listed Initials].&[XXX]
    )
    ,(
        [Ethical Wall].[Walled].&[False]
        ,[Ethical Wall].[White Listed Initials].&[]
    )
});
0


source






More articles:


All Articles