none
Make Null values remain null but treat zero values as 1?

    Question

  • Hi,

    I want to create a member as below:

    with member [Measures].[SampleCompliance] AS
    (
    CASE [Measures].[No Of Samples]
    WHEN Null Then Null
    ELSE
    IIF([Measures].[No Of Samples] = 0, 1, ([Measures].[No Of Passes])/[Measures].[No Of Samples])
    END
    )

    However this results in zeros being null as well as null values being null.  In my case null means the record should not be evaluated but zero means it should and as I am dealing with compliance 0 passes out of 0 Samples is 100% compliance so I need 0/0 to evaluate as 1.  Is there some way I can achieve this?

    Thanks,

    Dan

    Monday, March 10, 2014 10:30 PM

Answers

  • Hi Dan,

    Try use this

    member [Measures].[SampleCompliance] AS
     (
     CASE 
       WHEN IsEmpty([Measures].[No Of Samples]) = FALSE THEN
          CASE 
    	WHEN [Measures].[No Of Samples] = 0 THEN 1
    	ELSE [Measures].[No Of Passes]/[Measures].[No Of Samples]
           END
    END
    )
    or you can use IIF instead of CASE

    Tuesday, March 11, 2014 6:37 AM

All replies

  • Hi Dan,

    Try use this

    member [Measures].[SampleCompliance] AS
     (
     CASE 
       WHEN IsEmpty([Measures].[No Of Samples]) = FALSE THEN
          CASE 
    	WHEN [Measures].[No Of Samples] = 0 THEN 1
    	ELSE [Measures].[No Of Passes]/[Measures].[No Of Samples]
           END
    END
    )
    or you can use IIF instead of CASE

    Tuesday, March 11, 2014 6:37 AM
  • Try

    select case 0 when null then null when 0 then 1 else null end
    select case null when null then null when 0 then 1 else null end
    


    Many Thanks & Best Regards, Hua Min


    Tuesday, March 11, 2014 6:47 AM