I am in the process of creating a star and have a series of compute fields I need to build like ‘Qty per Day’, ‘Tons per Day’, etc.
The ‘Qty’ or ‘Tons’ fields are already available in the fact. The ‘Day’ needs to represent the ‘count of the days’ based on the users query or report as pulled from the Date dimension in the star.

If the user runs the report for a month then ‘Day’ in the star’s compute is the total number of the days in that month from the Date dimension in the star.

If the user select separate dates (say 4/12/2016; 4/15/2016; 4/20/2016) then ‘Days’ would be 3. And the compute for ‘Tons per Day’ in the star would be ‘Total Tons for the 3 Days specified’ divided by 3 (count of the dates selected); even if one of the days has NO tons in the fact.

Everything I have tried thus far either adjusts ‘Days’ based on which days had values or based on total count of the other sort fields in my test reports.

DATADIFF will not work because I can’t be assured users will select a string of dates.

The old SSAS cubes the users are being transitioned from handled this easily.

I am trying to provide similar fields the users have today and not make them do additional calcs in IA or Quick Data.

Any direction or examples would be much appreciated. Thanks.

This message has been edited. Last edited by: <Emily McAllister>,

How do they select the dates and what are the options they have to perform such selection ?

Knowing that may help us trying to gives you a solution 'cause with Focus, there is always (most of the time) more than one answer.

Users would mostly select the dates as filter on the DATE table in the Star/Business View.
The Star is fairly simple; 1 fact and about 10 dimensions.

The DATE dimension fields are typical; Full_Date, Month, Year, Accounting Month, Accounting Year, Week Number (in year), Current Day (0=today, 1=yesterday, 2= two days ago, etc.), Current Month, Current Year, etc. Users could select or combine any of these date fields as a filter. So the Compute in the master file will need to determine the total number of days selected in the query and pass that value to the 'DAYS' denominator of the master file compute.

I am thinking, since the users could be very creative in their report and filtering determining the total days used in the Compute need to be at the lowest level (every day = 1 and just total up the dates selected.)

As a side note:
The system being replaced is an SSAS cube where the designer (not me) used the Date table as both a Fact & Dimension and had a field called DAYS (with a value of 1 for each calendar day) the would be Total based on the period the user selected in the pivot. So even if the user selected dates that were not in a continuous range the Total Days used by the SSAS Field calculation was correct since it added up the number of days select.

Hope this provides some additional details. Thanks.
I need to find a way to just total the number of days select from the DATE table independent of the values and other attribute data.

What about using Count Distinct on the Day? esentially thats what you want isn't it? Unfortunately you won't be able to use count distinct on any other field... but it would work in the master.

Your date table should also be the parent table for your master join. and everything else should be a left outer. that will ensure that you don't lose any date values due to data not existing in the table.