SSAS count of non zero measure

Question

I am newbie to SSAS cube and need some help. I have a cube created from a fact table and one of the measure, lets call it amount, contains zeros. This measure was created as a SUM. Now I also have the Count measure added by SSAS designer. What I need is the count of all non zero amount. I tried to add a calculated measure as

`IIF([Measures].[amount] > 0,[Measures].[RowCount],null)`

also tried
FILTER([Measures].[RowCount],[Measures].[Amount] > 0) Both these returns the count including the amount=0. I am validating it via SSMS sql query SELECT count(*) FROM [dbo].[Fact_Session] where SiteKey = 5 and DateKey = 20170201 and Amount >0
Any help is appreciated. My assumption is that the IIF/Filter statement will operate on the individual rows before cubing, as once aggregated into a dimension, the amount will not be 0 due to the aggregation . Please Help.

whytheq whytheq · Accepted Answer · 2018-02-17T10:06:29

To count non-zero you can use this approach:

WITH 
MEMBER [Measures].[IsNotEmpty] AS
  IIf(
   Not(IsEmpty([Measures].[Amount]))
   ,1
   ,Null
  )

But better to do this inside a SUM over a particular set - so the following counts how many subcategories are not empty:

WITH 
MEMBER [Measures].[SubCategory_IsNotEmpty] AS
  SUM(
    [Product].[Product Categories].[SubCategory].members
   ,IIf(
      Not(IsEmpty([Measures].[Amount]))
      ,1
      ,Null
    )
  )

SSAS count of non zero measure

1 Answers