How to find the count of issues with EMPTY Case Numbers and NON EMPTY Case Numbers

We have list of Issues from which we would like to find out the ones which are Customer and the ones which are Non Customer.

Customer issues are identified with a Case Number and the ones reported internally do not have the Case Number. The Case Number field has been imported as an Issue Property. Now how I can represent a Logic like Count all issues where Case NOT Empty and Count all issues where Case Is Empty.

I have tried to create a query like this which is not working.

NonZero(
Count(
Filter(Descendants([Issue].CurrentMember,[Issue].[Issue]),
IsEmpty([Measures].[Issue Case])
)
)
)

Please help.

Try this:
Count(Filter(
Filter(Descendants([Issue].CurrentMember, [Issue].[Issue]),
[Measures].[Issues created] > 0),
[Measures].[Issue Case]=’(none)’
))
I am not sure why IsEmpty doesn’t work. The root cause probably is that ‘(none)’ value is not empty, isn’t it?

Hi @Arunava,

The formula does not have reference to existing measure, and therefore does not work as expected. A measure binds data together and allows to represent data from different points of view (Dimensions). Therefore, each calculated measure should contain at least one already existing measure to get the context of dimensions used in the report. The measure is a treasure!

To make this calculation work, you may add one more filtering criteria containing measure Issues created:

NonZero(
  Count(
    Filter(Descendants([Issue].CurrentMember,[Issue].[Issue]),
      IsEmpty([Measures].[Issue Case] AND
      --measure to add context of the report
      [Measures].[Issues created] > 0 )
    )  
  )
)

Thanks, @Alex_Cheng for the suggested solution to validate by value (none).
This solution works when the custom field “Case” is imported as a property and as a dimension. For the dimension, eazyBI generates value (none) to group issues without “Case”.

If the custom field “Case” is imported only as property, then should use IsEmpty() because issues without case have no value.

Best,
Zane / support@eazyBI.com

1 Like