Differentiate Null Values from 0s

[#1]

I  have a data warehouse with a million of rows and I want to calculate the fact Sales_Online for different dates. My Fact Table already have records but the however the metric Sales_Online still has the records to 0 € (not null!!!).

I try to differentiate the Null Values from 0s because when I don’t have data I want to show a message error. For that I’m using the following expression in a Object Text:

    =IF(isNull(SALES_ONLINE),’NO_DATA’,num(SUM(SALES_ONLINE)/1000,'#,##')&'K')

But, this expression returns the message error even if I selected a day that I’ve records with 0 €.

Thanks!!!

[sunny_talwar]

What about this?

If(Len(Trim(Avg(SALES_ONLINE))) = 0, 'NO_DATA', Sum(SALES_ONLINE)/1000)

[#3]

Hi Sunny T,

Thanks for your response But I think it doens't returns my goal

I' give you a example to represent my problem.

Look at this image:

As you can see all of my Sales_Online is equal to 0. The logic is:

• If a I select a date that exists in Fact Table the result is 0 because the Sum(Sales_Online) = 0;
• But I select another date the result should be -1 (or Null) because I'm adding missing values.

Are you undestanding my trouble?

Thanks for you help