0

Count dimensions with condition

Hi, for the table in the picture, how to count "Site ID" for each region, and count "Site ID" whose score greater than 6? (this is from a cube)

1reply Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • Hi Lei Zhou,

    There may be a simpler answer, but here's a slightly involved way of going about it. I've used our demo database with attributes of Countries and States.

    We need to count the states that exist for each Country, then also count the states for each country where sales >500,000

    Create a set of states for each Country in my query, and Count how many there are:

    COUNT(
    EXISTS(

    DESCENDANTS( [Customer].[State].[All] , [Customer].[State].[State]) , [Customer].[Country].CURRENTMEMBER, "Metrics" )
      )     

    Do the same, but this time add a FILTER to only keep states whose sales are greater than 500,000

    FILTER(

    COUNT(
    EXISTS(

    DESCENDANTS( [Customer].[State].[All] , [Customer].[State].[State]) , [Customer].[Country].CURRENTMEMBER, "Metrics" )
      ) , [Measures].[Sales]>500000

    )

    Have Countries and States on the rows, Measures on the columns.

    Select all Countries and All States plus the two calculated members, count of states and count of filtered states:

     You could, of course, replace the absolute value for sales with a Variable to make this more dynamic.

    Hope that helps.

    Ian    

    Reply Like 1
login to reply
Like Follow
  • Status Answered
  • 1 mth agoLast active
  • 1Replies
  • 105Views
  • 2 Following