none
MDX Measure - Count based on date RRS feed

  • 질문

  • Hi All,

    I am trying to develop a measure to count the number of times something happens daily. This is the condition:

    IIF([Measures].[Sstk Qty] > 0

    AND[Measures].[A+C Quantity] <= 0 ,1, NULL)

    So I want to be able to figure out how many times this happens per month, or week, or quarter, or whatever date slicer I use. Right now this only produces 1 or Null for any given data level. So if this condition was true 9 times in the month of Novemeber and I had month in my query, I would see 9 as the answer. Currently I see 1.

    Thanks,

    Mike

    2019년 11월 18일 월요일 오후 10:49

답변

  • I ended up using the above calculation and called it X. Then I used this to get the desired outcome

    Sum
        (
          Descendants
          (
            [Date].[Fiscal Hier].CurrentMember,
           ,Leaves
          )
         ,([Measures].[X])
        )

    • 답변으로 표시됨 Mike Alb 2019년 11월 19일 화요일 오전 12:36
    2019년 11월 19일 화요일 오전 12:36

모든 응답

  • I ended up using the above calculation and called it X. Then I used this to get the desired outcome

    Sum
        (
          Descendants
          (
            [Date].[Fiscal Hier].CurrentMember,
           ,Leaves
          )
         ,([Measures].[X])
        )

    • 답변으로 표시됨 Mike Alb 2019년 11월 19일 화요일 오전 12:36
    2019년 11월 19일 화요일 오전 12:36
  • Is this condition something that needs to be checked on a row by row basis? If so the best approach is to implement this logic in an expression in a SQL view or as a calculated column in your DSV. Then you can simply create a standard sum based measure over this new column.

    http://darren.gosbell.com - please mark correct answers

    2019년 11월 19일 화요일 오전 12:52
    중재자
  • Thanks for the reply Darren. The measures are coming from different fact tables so this wouldn't be an option.
    2019년 11월 19일 화요일 오전 2:33