Hitachi Vantara Pentaho Community Forums
Results 1 to 2 of 2

Thread: Dbout about calculated members. Counting the number of facts that match a condition

  1. #1
    Join Date
    Jun 2013

    Unhappy Dbout about calculated members. Counting the number of facts that match a condition

    Hi, I'm a newbie and I need some help with one query. Unfortunately I couldn't find the solution by myself.

    My desired output: the number of ratings that are bigger than 3. What I actually get: the total number of user ratings in case that the average of the ratings is over 3.

    This is my query, based in a suggestion that I saw in this forum:

    With Member [Measures].[Number of possitive ratings] as IIF( [Measures].[User Rating] > 3, [Measures].[Number of Users Rating], 0)SELECT NON EMPTY[Measures].[Number of possitive ratings] ON COLUMNS,[Movie Year].[All Years] ON ROWSFROM [Rating]When I replace [Measures].[Number of Users Rating] for 1, then I get 1 in case the average is over 3. I don't like the idea of creating a new measure in the cube just for this.

    Thanks a lot!

  2. #2
    Join Date
    Jan 2013


    Just to make sure I understand, you want to get a count of individual fact rows with a rating value greater than 3? If so, that's a bit tricky, since MDX can only act on fact data rolled up at the lowest granularity of all attributes in your cube. E.g. if you have just two dimensions [Customer] and [Product], it's possible to come up with conditional counts like you have above for each individual customer and product, but a single customer may have rated the same product multiple times, and there's no way to "split apart" that information since the granularity of the cube doesn't go that low.

    Could you treat [User Rating] as a dimension? You could then do something like:

    Aggregate( {[User Rating].[4], [User Rating].[5]}, [Measures].[Number of Users Rating])

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2017 Pentaho Corporation. All Rights Reserved.