DISTINCT COUNT WITH NULL VALUES (GRAND TOTAL)

Hello,

I have a DB of professors and information related with them. I created the cube, it consist of:

Measures:

Measure group Professors:

Amount of projects (COUNT proj_id)

Amount of pulications (COUNT pub_id)

Amount of e_books (COUNT book_id)

--

Measure group Projects:

Distinct amount of projects (DISTINCT COUNT proj_id)

--

Measure group Publications:

Distinct amount of publications (DISTINCT COUNT pub_id)

--

Measure group E_books:

Distinct amount of e_books (DISTINCT COUNT book_id)

Calculated measures:

Amnt_Projects

iif ([Measures].[ Amount of projects ] = 0 OR [Measures].[ Amount of projects] = NULL,0,[Measures].[ Distinct amount of projects])

Amnt_Publications

(similar to the above one)

Amnt_E_books

(similar to the above one)

Dimensions:

dimPROFESSORS

- prof_id

-surname

-name

-gender

dimPROJECTS

- proj_id

-type name

-name

dimPUBLICATIONS

- pub_id

-type name

-name

dimE_BOOKS

- book_id

-name

Data_Projects

-data_id

-years

Data_Publications

-data_id

-years

Data_E_books

-data_id

-years

For example, when I browse the cube:

prof_id Amount of projects Distinct amount of projects Amnt_Projects

1032 30 1 1

1070 90 2 2

1111 0 1 0

1137 0 1 0

1234 1404 9 9

1721 504 7 7

2661 85 5 5

... ... ... ...

6999 20 1 1

9956 50 5 5

Uknown 0

Grand Total 2421 11 11

Grand Total “11“ is the amount of distinct projects +1 (because of the unknown member). So the last column shows the right amount of projects for the professor but I want Grand Total to sum those values and show, how many projects do the professors have (it should be ?59“ for all professors). How could I get the right value to be shown in Grand Total?

Any help would be appreciated as I'm very new to the MDX.

|||

Maybe you need to sum the project counts for each professor in the selection, like:

Amnt_Projects

Sum(existing [DimProfessors].[prof_id].[prof_id], [Measures].[ Distinct amount of projects])

|||Thank You Deepak. This calculation works perfectly, just i added my calculated measure expression instead of [Measures].[ Distinct amount of projects]. I am very happy that at last it works!!!
|||Hello again. I was working with KPI's and noticed, that this calculation needs to be improved. When I try to filter by any particular professor, the Grand Total remains the same - for the projects always "59". How could I change the calculateted member?

Thank You in advance.
|||

Were you testing the KPI with the KPI Browser in BIDS? If so, please check directly with an MDX query, since the browser may be using a subselect vs. where. The query would be like:

select

KPIValue("ProfCalc") on 0

from ProfCube

where [DimProfessors].[prof_id].&

|||I tested Your SQL query, and You are right. This query works fine. But how can I make the KPI browser in BIDS show the right values (I use the Amnt_Projects calculation for the KPIValue expression)? The same with cube browser:

prof_id Amnt_Projects
1032 1
1070 2
1111 0
Grand Total 59

Thank You again. I appreciate Your help very much!
|||

In that case, a different approach may be needed, based on a recent Forum post:

- Create a new "row count" measure called Amnt_Projects for the Professors measure group.

- Add a statement to the cube MDX script, assigning values at the DimProfessors leaf level to Amnt_Projects:

([DimProfessors].[prof_id].[prof_id], [Measures].[Amnt_Projects]) = [Measures].[ Distinct amount of projects];

|||I don't know if I'm doing something wrong, but in this case I get #VALUE! for every professor.
First, I created a row count measure Amnt_Projects in the Professors measure group. Than I created calculated measure as You wrote and named it Amnt_projects2.

What is more, I am creating reports using this cube. I tried to make the one similar to the "Teritory Sales Drilldown" example. But the strange thing is with Amnt_Projects (the calculation, that You provided earlier). I make this kind of drilldown: Professor (Name/Surname) and Amnt_Publications->Type of Publication and Amnt_Publications->Name of Publication and Amnt_Publications

I get the results:
Professor Type of Publication Name of Publication Amnt_Publication
Professor1 #Error
Type1 0
Name1 0
Name2 0
Type2 #Error
Name3 1
Name4 0

So when the value of Amnt_Publication is 0, everything is ok, but when it has to sum one's, it shows #Error.

Thank You!
|||By the way, I get this cind of warning in the reporting services when I preview the report:
"The Value expression for the textbox ‘Amnt_Publications’ uses an aggregate function on data of varying data types. Aggregate functions other than First, Last, Previous, Count, and CountDistinct can only aggregate data of a single data type."
|||I have just notices that using distinct count fits me very well in this reporting services situation. It behaves very differently than in Analysis Services browser. In Reporting Services it counts distinct values and shows null value for those professors that have no publications. And in Analysis Services it shows "1" for null value. Indeed strange.

Still would appreciate Your help with those Amnt in Analysis Services, which I use for KPI value and browse in KPI browser.

Thank You!
|||"I created calculated measure as You wrote and named it Amnt_projects2" - in the approach which I suggested, there is no calculated measure. The cube script assignment applies to the new cube measure: "Amnt_Projects".|||Thank You for answering. But could You be more specific? (about "The cube script assignment applies to the new cube measure: "Amnt_Projects". ")This is my first try with SQL Server and I have only a couple of days to finish this.

What is more, I need those KPI using not the whole Amount of projects, but something like this:

KPI for projects = Amount of Type1 projects*0,6 + Amount of Type2projects *0,3 + Amount of Type3*0,1

as I mentioned, I have such kind of dimension Projects:

dimPROJECTS

- proj_id

-type name (there are three Types of projects)

-name (the name of project itself)

Thank You very much!
|||OK, I have just assigned the KPI value by myself and it works.Of course it works only in MS SQL Server Manegement Studio..I tested it by SQL query and it should work in Reporting Services. But because I'm still using that previous Amnt_projects -->Sum(existing [DimProfessors].[prof_id].[prof_id], [Measures].[ Distinct amount of projects]), it doesn't work properly in KPI browser. The KPI Value expresion is:

SUM([Dim Projects].[Type Name].&[Type1],[Measures].[Amnt_Projects])* 0.6 + SUM([Dim Projects].[Type Name].&[Type2],[Measures].[Amnt_Projects])* 0.3 + SUM([Dim Projects].[Type Name].&[Type3],[Measures].[Amnt_Projects]) * 0.1

So I would appreciate Your explanation about "The cube script assignment applies to the new cube measure: "Amnt_Projects". "

Thank You in advance!
|||

Not sure whether you reviewed the earlier post, which I provided a link to - but the approach I suggested is similar:

- Create a new "row count" measure called Amnt_Projects for the Professors measure group (this replaces the calculated measure: Amnt_Projects)

- Add this statement to the cube MDX script, assigning values at the DimProfessors leaf level to Amnt_Projects (it doesn't create any new measures):

([DimProfessors].[prof_id].[prof_id], [Measures].[Amnt_Projects]) = [Measures].[ Distinct amount of projects];

DISTINCT COUNT WITH NULL VALUES (GRAND TOTAL)

Hello,

I have a DB of professors and information related with them. I created the cube, it consist of:

Measures:

Measure group Professors:

Amount of projects (COUNT proj_id)

Amount of pulications (COUNT pub_id)

Amount of e_books (COUNT book_id)

--

Measure group Projects:

Distinct amount of projects (DISTINCT COUNT proj_id)

--

Measure group Publications:

Distinct amount of publications (DISTINCT COUNT pub_id)

--

Measure group E_books:

Distinct amount of e_books (DISTINCT COUNT book_id)

Calculated measures:

Amnt_Projects

iif ([Measures].[ Amount of projects ] = 0 OR [Measures].[ Amount of projects] = NULL,0,[Measures].[ Distinct amount of projects])

Amnt_Publications

(similar to the above one)

Amnt_E_books

(similar to the above one)

Dimensions:

dimPROFESSORS

- prof_id

-surname

-name

-gender

dimPROJECTS

- proj_id

-type name

-name

dimPUBLICATIONS

- pub_id

-type name

-name

dimE_BOOKS

- book_id

-name

Data_Projects

-data_id

-years

Data_Publications

-data_id

-years

Data_E_books

-data_id

-years

For example, when I browse the cube:

prof_id Amount of projects Distinct amount of projects Amnt_Projects

1032 30 1 1

1070 90 2 2

1111 0 1 0

1137 0 1 0

1234 1404 9 9

1721 504 7 7

2661 85 5 5

... ... ... ...

6999 20 1 1

9956 50 5 5

Uknown 0

Grand Total 2421 11 11

Grand Total “11“ is the amount of distinct projects +1 (because of the unknown member). So the last column shows the right amount of projects for the professor but I want Grand Total to sum those values and show, how many projects do the professors have (it should be ?59“ for all professors). How could I get the right value to be shown in Grand Total?

Any help would be appreciated as I'm very new to the MDX.

|||

Maybe you need to sum the project counts for each professor in the selection, like:

Amnt_Projects

Sum(existing [DimProfessors].[prof_id].[prof_id], [Measures].[ Distinct amount of projects])

|||Thank You Deepak. This calculation works perfectly, just i added my calculated measure expression instead of [Measures].[ Distinct amount of projects]. I am very happy that at last it works!!!
|||Hello again. I was working with KPI's and noticed, that this calculation needs to be improved. When I try to filter by any particular professor, the Grand Total remains the same - for the projects always "59". How could I change the calculateted member?

Thank You in advance.
|||

Were you testing the KPI with the KPI Browser in BIDS? If so, please check directly with an MDX query, since the browser may be using a subselect vs. where. The query would be like:

select

KPIValue("ProfCalc") on 0

from ProfCube

where [DimProfessors].[prof_id].&

|||I tested Your SQL query, and You are right. This query works fine. But how can I make the KPI browser in BIDS show the right values (I use the Amnt_Projects calculation for the KPIValue expression)? The same with cube browser:

prof_id Amnt_Projects
1032 1
1070 2
1111 0
Grand Total 59

Thank You again. I appreciate Your help very much!
|||

In that case, a different approach may be needed, based on a recent Forum post:

- Create a new "row count" measure called Amnt_Projects for the Professors measure group.

- Add a statement to the cube MDX script, assigning values at the DimProfessors leaf level to Amnt_Projects:

([DimProfessors].[prof_id].[prof_id], [Measures].[Amnt_Projects]) = [Measures].[ Distinct amount of projects];

|||I don't know if I'm doing something wrong, but in this case I get #VALUE! for every professor.
First, I created a row count measure Amnt_Projects in the Professors measure group. Than I created calculated measure as You wrote and named it Amnt_projects2.

What is more, I am creating reports using this cube. I tried to make the one similar to the "Teritory Sales Drilldown" example. But the strange thing is with Amnt_Projects (the calculation, that You provided earlier). I make this kind of drilldown: Professor (Name/Surname) and Amnt_Publications->Type of Publication and Amnt_Publications->Name of Publication and Amnt_Publications

I get the results:
Professor Type of Publication Name of Publication Amnt_Publication
Professor1 #Error
Type1 0
Name1 0
Name2 0
Type2 #Error
Name3 1
Name4 0

So when the value of Amnt_Publication is 0, everything is ok, but when it has to sum one's, it shows #Error.

Thank You!
|||By the way, I get this cind of warning in the reporting services when I preview the report:
"The Value expression for the textbox ‘Amnt_Publications’ uses an aggregate function on data of varying data types. Aggregate functions other than First, Last, Previous, Count, and CountDistinct can only aggregate data of a single data type."
|||I have just notices that using distinct count fits me very well in this reporting services situation. It behaves very differently than in Analysis Services browser. In Reporting Services it counts distinct values and shows null value for those professors that have no publications. And in Analysis Services it shows "1" for null value. Indeed strange.

Still would appreciate Your help with those Amnt in Analysis Services, which I use for KPI value and browse in KPI browser.

Thank You!
|||"I created calculated measure as You wrote and named it Amnt_projects2" - in the approach which I suggested, there is no calculated measure. The cube script assignment applies to the new cube measure: "Amnt_Projects".|||Thank You for answering. But could You be more specific? (about "The cube script assignment applies to the new cube measure: "Amnt_Projects". ")This is my first try with SQL Server and I have only a couple of days to finish this.

What is more, I need those KPI using not the whole Amount of projects, but something like this:

KPI for projects = Amount of Type1 projects*0,6 + Amount of Type2projects *0,3 + Amount of Type3*0,1

as I mentioned, I have such kind of dimension Projects:

dimPROJECTS

- proj_id

-type name (there are three Types of projects)

-name (the name of project itself)

Thank You very much!
|||OK, I have just assigned the KPI value by myself and it works.Of course it works only in MS SQL Server Manegement Studio..I tested it by SQL query and it should work in Reporting Services. But because I'm still using that previous Amnt_projects -->Sum(existing [DimProfessors].[prof_id].[prof_id], [Measures].[ Distinct amount of projects]), it doesn't work properly in KPI browser. The KPI Value expresion is:

SUM([Dim Projects].[Type Name].&[Type1],[Measures].[Amnt_Projects])* 0.6 + SUM([Dim Projects].[Type Name].&[Type2],[Measures].[Amnt_Projects])* 0.3 + SUM([Dim Projects].[Type Name].&[Type3],[Measures].[Amnt_Projects]) * 0.1

So I would appreciate Your explanation about "The cube script assignment applies to the new cube measure: "Amnt_Projects". "

Thank You in advance!
|||

Not sure whether you reviewed the earlier post, which I provided a link to - but the approach I suggested is similar:

- Create a new "row count" measure called Amnt_Projects for the Professors measure group (this replaces the calculated measure: Amnt_Projects)

- Add this statement to the cube MDX script, assigning values at the DimProfessors leaf level to Amnt_Projects (it doesn't create any new measures):

([DimProfessors].[prof_id].[prof_id], [Measures].[Amnt_Projects]) = [Measures].[ Distinct amount of projects];

sql

Distinct count problem

Hi,

I'm trying to create a measure (distinct count) for counting the number of stores which sell product. I used to fact table to count store_key but I've just realized this measure returns a count including stores which do not sell the product.

Daily Sales fact : store_key, net sales dollars .....

How should I define the calc in this case? I'm thinking conditional statements but not clear yet.

Please give me some comments. Thanks.

We do something similar - a count of invoices that have a coupon applied to them. There are two ways (that I know of) to do this:

1) at the fact level. Have a measure in your fact table that contains a 1 or 0 for each store that sells the product. Not a very efficient way, but it will get the job done. Do a sum on this measure. Depending on how you want to count your products, you could have a measure group that is store_key, product_key, sells_product (int). This will work, but definitely not efficient.

2) The way we do it is to make the item you are measuring an attribute of the dimension itself. In our case we have an invoice dimension which contains a "has_coupon" attribute. As part of the ETL we determine if the value of has_coupon shoud be "Yes" or "No". You then can query according to the values of this attribute. We then can do [Invoice].[Has Coupon].Children.Count, or any other derivation of it.

I would recommend option #2, but it all depends on your situation. If anyone else knows of other ways to model, I'm curious too.

Good luck,

John Hennesey

|||Not totally clear, but is it counting a NULL value when you don't want it to?

Just create another fact table in the DSV based off of the fact table where the key is not null:

select *
from fact
where
key is not null

And create the DISTINCT COUNT measure based of this new new table.
|||

thanks.

it's easy and it works fine

sql

Distinct Count in a new Measure Group

Here is the situation:

I have a cube that has transaction level data and there is a control number that goes with this data, but it isn't one for one. If I have 60 million rows in the fact table, I have 50 million control numbers in a control number dimension.

I would like to be able to slice the data by date (for instance) and then do a distinct count on the control number dimension. I can do a distinct count w.o having to do a join of course because I can count the distinct instances of the key to the control number dimension.

One more note - the control number dimension is not a degenerate dimension...it is not one to one on the fact table.

I would like to use the DISTINCT COUNT measure in SSAS, but it makes me create a new measure group to do so. All of my additive measures are in one group, the distinct count is forced into another group. This forces double the processing time.

Does anyone know why this behavour happens and can someone explain why a DISTINCT COUNT measure has to go in a seperate measure group?

Thanks,

Mark

Mark,

Do you already have a DISTINCT COUNT measure in that measure group? Analysis Services will only allow you to have one DISTINCT COUNT per measure group.

|||Maybe this MDX will overcome the limitation of one unqiue count.

Distinct(Filter([Customer].[Customer Name].Members,Not IsEmpty([Measures].[Sales Amount]))).Count

Timmy|||

Actually, I don't have one in the first measure group. The first measure group is just regular measures, but if I create a new measure that is a distinct count measure, it automatically puts it in another group.

I have now heard from other sources that this is an exptected behavior. So no biggie, just needed to know.

ps - sorry it took so long to respond.

Thanks,

Mark

http://spaces.msn.com/mgarnerbi

|||I noticed this too, does anyone know the underlying reason why this is? Is this going to be fixed in a future version?sql

Distinct Count in a new Measure Group

Here is the situation:

I have a cube that has transaction level data and there is a control number that goes with this data, but it isn't one for one. If I have 60 million rows in the fact table, I have 50 million control numbers in a control number dimension.

I would like to be able to slice the data by date (for instance) and then do a distinct count on the control number dimension. I can do a distinct count w.o having to do a join of course because I can count the distinct instances of the key to the control number dimension.

One more note - the control number dimension is not a degenerate dimension...it is not one to one on the fact table.

I would like to use the DISTINCT COUNT measure in SSAS, but it makes me create a new measure group to do so. All of my additive measures are in one group, the distinct count is forced into another group. This forces double the processing time.

Does anyone know why this behavour happens and can someone explain why a DISTINCT COUNT measure has to go in a seperate measure group?

Thanks,

Mark

Mark,

Do you already have a DISTINCT COUNT measure in that measure group? Analysis Services will only allow you to have one DISTINCT COUNT per measure group.

|||Maybe this MDX will overcome the limitation of one unqiue count.

Distinct(Filter([Customer].[Customer Name].Members,Not IsEmpty([Measures].[Sales Amount]))).Count

Timmy
|||

Actually, I don't have one in the first measure group. The first measure group is just regular measures, but if I create a new measure that is a distinct count measure, it automatically puts it in another group.

I have now heard from other sources that this is an exptected behavior. So no biggie, just needed to know.

ps - sorry it took so long to respond.

Thanks,

Mark

http://spaces.msn.com/mgarnerbi

|||I noticed this too, does anyone know the underlying reason why this is? Is this going to be fixed in a future version?

Distinct Count in a new Measure Group

Here is the situation:

I have a cube that has transaction level data and there is a control number that goes with this data, but it isn't one for one. If I have 60 million rows in the fact table, I have 50 million control numbers in a control number dimension.

I would like to be able to slice the data by date (for instance) and then do a distinct count on the control number dimension. I can do a distinct count w.o having to do a join of course because I can count the distinct instances of the key to the control number dimension.

One more note - the control number dimension is not a degenerate dimension...it is not one to one on the fact table.

I would like to use the DISTINCT COUNT measure in SSAS, but it makes me create a new measure group to do so. All of my additive measures are in one group, the distinct count is forced into another group. This forces double the processing time.

Does anyone know why this behavour happens and can someone explain why a DISTINCT COUNT measure has to go in a seperate measure group?

Thanks,

Mark

Mark,

Do you already have a DISTINCT COUNT measure in that measure group? Analysis Services will only allow you to have one DISTINCT COUNT per measure group.

|||Maybe this MDX will overcome the limitation of one unqiue count.

Distinct(Filter([Customer].[Customer Name].Members,Not IsEmpty([Measures].[Sales Amount]))).Count

Timmy
|||

Actually, I don't have one in the first measure group. The first measure group is just regular measures, but if I create a new measure that is a distinct count measure, it automatically puts it in another group.

I have now heard from other sources that this is an exptected behavior. So no biggie, just needed to know.

ps - sorry it took so long to respond.

Thanks,

Mark

http://spaces.msn.com/mgarnerbi

|||I noticed this too, does anyone know the underlying reason why this is? Is this going to be fixed in a future version?

Distinct Count Custom Rollup

I have a vehicleID in my fact table. I want to create a measure called VehicleCount. This would be a distinct count of vehicles per day, but would be a sum of the per day counts for months, years, etc. If I just use distinct count on the VehicleID field will that give me the per day count and how do I make it so that the rollup is a sum instead of doing a distinct count for the higher levels (month, year, etc?)

I'm using SSAS2005.

Thanks for any help.

One approach would be to create a "distinct count" measure like [DistinctVehicles] on the VehicleID field, then sum its daily values in [VehicleCount] measure:

Create [VehicleCount] as a "count" measure on the fact table, and [DistinctVehicles] as a "distinct count" measure on the VehicleID field. Add an assignment to the cube MDX script like: ([Measures].[VehicleCount], [Date].[Date].[Date]) = [Measures].[DistinctVehicles];|||

Thanks for the response.

I'm a slightly confused though. You said, "Create [VehicleCount] as a "count" measure on the fact table." Shouldn't this be a "Sum" measure? Otherwise wouldn't you just end up with the count of days?

Thanks for your help.

|||Should work either way (I did test with a "count" measure), because the [Day] level is overwrtten by the "distinct count" assignment; and those daily values should get summed up at higher [Date] levels ...sql

DISTINCT Count and NULL Processiong

In short, I have a measure that counts the distinct number of customers.

I recently learned that it's counting a NULL value as 1 and I don't want this. Are there some properties that can stop the NULL value from being counted? I tried playing with a few of the properties that I found, but no luck.

The only solution that I have thought of so far is creating another table in the DSV that explicitly filters out the NULL values in the WHERE clause and then using it as the source for the DISTINCT COUNT. However, I'd like to avoid this solution if possible.

Maybe some MDX in a Calculated Member is needed? If so, please provide the pseudo-MDX as my MDX is definitely weak.

Any thoughts?

By the way, it's a AS2005 cube using SQL 2005 Standard Edition.

How about creating a new, hidden dimension (or attribute on your customer dimension) with two members: one member represents rows in the fact table where your Customer is null, and one for where it's not null. What you could then do is keep your distinct count measure as it is, but then overwrite it in the MDX Script so it only returns the value for the not-null member - something like this:

(Measures.[Customer Count])=(Measures.[Customer Count], [Hidden Dimension].[Hidden Attribute].[Not Null Customers]);

HTH,

Chris

|||Or, even better, make [Not Null Customers] member to be a default member in that dimension, and then you won't need to do anything in MDX Script at all, and it will be better for performance. My preference, however, is to do the DSV change that you want to avoid, I think that is the best solution.

DISTINCT COUNT - unhelpful error message

Hi,
I need to return a distinct count of customers who have ordered goods.
To do this I created a measure of type DISTINCT COUNT, over the CustomerID field in the orders fact table. Being a foreign key for the customers dimension table, the customerID is integer, not-null, and therefore ideal for the purpose.
The measue is created in a new measure group OK, and the cube processes through OK. However when I come to view the data by dragging the new measure onto the columns in the VS browser I get the wonderful message:

"
The query could not be processed: o Internal error: An unexpected exception occured.

"
Doing exactly the same creation process with the ProductID field works fine, and gives the expected results. I've looked at the tables and can see nothing obviously wrong with the data. The only difference is that there are about 3,800 products, and about 1,000,000 customers. However I did the same thing on the prototypes with no problem at all. Version is SQL 2005 Enterprise Ed.
Any thoughts?
Thanks as always,

Richard

Suggest you contact Analysis Services customer support for this situation.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Displaying various Time Intelligence calculations at the same time

I created a new calculation for a measure to display Year over Year growth % using the business intelligence wizard which created new calculated members in my time dimension. How can I display the new calculation and the original value at the same time without these two options being columns or rows? For example:

2005

Sales Sales Year over Year Growth %

Product A $23 1%

Product B $45 15%

I have tried to do this using perspectives but these are not included in the 'Standard Edition' SKU. Thanks in advance.

Here is an example that should help you (from Foodmart 2000)

with
member [Measures].[Prev Sales] as '([Measures].[Unit Sales],[Time].currentmember.lag(1))'
member [Measures].[% Increase] as 'iif([Measures].[Prev Sales]<>0,100*([Measures].[Unit Sales]-[Measures].[Prev Sales]) / [Measures].[Prev Sales],0)'

select crossjoin({[Time].[1997].[Q3],[Time].[1997].[Q4]},{[Measures].[Unit Sales],[Measures].[% Increase]}) on columns,
{[Product].[Product Name].members} on rows
from sales

Hope this helps,

Santi