icCube MDX on distinct count in relation to same dimension in axis or filter

Question

I have the following MDX statement on the $Monitoring cube:

 WITH
MEMBER [Measures].[Unique Users] AS distinctcount(([User].[User].[User L].members
    , [Measures].[Open Report Count])),format_string="#,#0"
SELECT
NON EMPTY { {[Measures].[Unique Users],[Measures].[Open Report Count]} } ON COLUMNS,
NON EMPTY { [Time].[Time].[Day L] } ON ROWS
 FROM ( SELECT 
{ lag(strtomember("[Time].[Time].["+right("0"+str(day(SchemaRefreshTime())),2) +"-"+ right("0"+str(month(SchemaRefreshTime())),2) + "-"+str(year(SchemaRefreshTime()))+ "]"), 6):strtomember("[Time].[Time].["+right("0"+str(day(SchemaRefreshTime())),2) +"-"+ right("0"+str(month(SchemaRefreshTime())),2) + "-"+str(year(SchemaRefreshTime()))+ "]") } ON 0 FROM [$Monitoring])

/*ic3navigation*/
axis 1 NON EMPTY order(nonempty(Descendants([Report].[Report], ,leaves),[Open Report Count]),[Open Report Count],desc)
FILTERBY /*ic3*/ {[Time].[Time].[ALL].&[2015].&[2015-11-27].&[27-11-2015]}
FILTERBY /*ic3*/ {[User].[User].[All Users].&[<user>]}

*) change <user> with the actual user name
*) the ...lag.. formula is used to give the last 7 days based on schema refresh time
***) this MDX query can be run on any $Monitoring cube if you have filled in an existing user

I would expect the distinctcount function to take into account the FILTERBY. So the result should be 1 (there is just one user selected). The strange thing is, that it does not. It shows more than one user, so I assume the FILTERBY on users is not taken into account for the distinctcount.

The same thing happens when I move the FILTER BY to the AXIS or to the ROWS or COLUMNS.

Is this a bug or is this something how MDX/ MDX++ works in icCube?

Please advise.

ic3 ic3 · Accepted Answer · 2015-12-08T13:49:30

It's the expected behaviour. Welcome to advanced MDX !

A FilterBy is exactly the same as a sub-select.

Members are not filtered by a subselect or a where clause in a calculated members.
In a calculated member, a tuple that defines a hierarchy will 'overwrite' the one defined in a subquery or where clause.

------ UPDATE ------

If you want to filter the set with the where clause/subselect you've the EXISTING operator.

MEMBER [Measures].[Unique Users] AS count( Existing [User].[User].[User L].members),format_string="#,#0"

if you want only users with data for the cell tuple :

MEMBER [Measures].[Unique Users] AS count( nonempty( Existing [User].[User].[User L].members, [Measures].[Open Report Count])),format_string="#,#0"

If you've a large number of users I'd advise adding a measure [Distinct Users] that is a distinct count on the user id. This way you avoid all complexity that we're facing here.

icCube MDX on distinct count in relation to same dimension in axis or filter

1 Answers