clarkcj1


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.





Re: Distinct Count Custom Rollup

Deepak Puri


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];






Re: Distinct Count Custom Rollup

clarkcj1

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.







Re: Distinct Count Custom Rollup

Deepak Puri

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 ...