How to filter a measure based on another measure

0
votes

I have a dimension table called DimUser that has 1 row per user. I have a measure based on this dimension called "USER COUNT" which is count of rows of the DimUser table. I also have a calculated measure called "ACTIVE DAYS" which returns no of days a user is active.

Now, I want to create another calculated measure based on this 2 measures, where I count only users who are active more than 5 days. I have total 5 users in my user table and 2 users in my fact table who are active more than 5 days. My MDX expression should return 2.

This is what I wrote

FILTER([Measures].[USER COUNT], [Measures].[ACTIVE DAYS] > 5)

But this gives me 5 as the answer instead of 2. What am I doing wrong?

Next I tried this, but this fails compilation saying two measures in the expression are not allowed

([Measures].[USER COUNT], [Measures].[ACTIVE DAYS] > 5)

Next, I tried creating a new calculated member called [IsActive] that returns true or false if active days > 5 and then use that as filter on the [Measures].[USER COUNT].

How do I go about doing this?

2
sqlssasmdxolapolap-cube
Is there a User dimension in your cube? With a UserId level? – whytheq
Yes. I have DimUser table which becomes a user dimension in the cube. It has two columns in it. {UserId, UserName}. But keep in mind that my fact table will have multiple rows for same user and the DimUser has a many-many relationship with the fact's measure group so that we can calculate distinct count based on count of rows of user dimension – user330612

2 Answers

2
votes

First of all, I don't think it is even possible to filter one measure on another measure, because measures are numeric values, not a set. The concept of filtering applies to a set. So filtering a measure based on another measure is absurd. What can be done though, in terms of MDX, is to create a set of users with the first filter applied(i.e. [Measures].[ACTIVE DAYS] > 5) and then getting a COUNT out of it.

Something like this(NOT TESTED):

WITH SET [ActiveUsersMoreThan5Days] AS
FILTER([User].[UserId].CHILDREN, [Measures].[ACTIVE DAYS] > 5)
//Assuming the User dimension and hierarchy here..
//Fill it in with actual..

MEMBER [Measures].CountActiveUsersMoreThan5Days AS
COUNT(NONEMPTY([ActiveUsersMoreThan5Days], [Measures].[<<Some other measure>>]))

SELECT [Measures].CountActiveUsersMoreThan5Days on 0
//,[Dimension].Hierarchy.CHILDREN ON 1
FROM [Cube]
1
votes

To avoid using Filter which is iterative and not the best performing function. Also to create a measure that is context aware then the following might help:

WITH 
  MEMBER [Measures].[CntUsersActive5DaysOrMore] AS 
    Sum
    (
      [UserId].[UserId] //<<< or [UserId].[UserId].[UserId]
     ,IIF
      (
        [Measures].[ACTIVE DAYS] > 5
       ,1
       ,0
      )
    ) 
SELECT 
  [Measures].[CntUsersActive5DaysOrMore] ON 0
FROM [YourCube];