Filtering a SET with two dimensions

Question

I have a fact table say WhsFactJob where we have CreatedTimeKey and InProgressTimeKey and I have a measure where Job Count.

I am trying to create a Calculated measure which will fetch me the job count whose CreatedTimeKey falls between 1(1:00 AM) and 2(2:00 AM) and InProgressTimeKey also falls with the same 1(1:00 AM) and 2(2:00 AM).

I tried to use SUM and Except, But it errors out saying Except has to use the same hierarchy.( CreatedTimeKey and InprogressTime Key are two seperate dimensions.

Any suggestions would help.

Here is the MDX example I am trying out.

WITH 
MEMBER [Measures].[Sum] AS 
   SUM(
      EXCEPT(
         {[Created Time].[Hour].&[0]:[Created Time].[Hour].&[14]},
         {[In Progress Time].[Hour].&[0]:[In Progress Time].[Hour].&[14]}
      )
     ,[Measures].[Job Count]
   ) 
SELECT 
    [Measures].[Sum] ON 0 
FROM [Cube]

Answer 1

I'm not clear why you're using EXCEPT. How about this:

SUM
(
CROSSJOIN(
  {[Created Time].[Hour].&[0]:[Created Time].[Hour].&[14]},
  {[In Progress Time].[Hour].&[0]:[In Progress Time].[Hour].&[14]}
          )
,[Measures].[Job Count]
)