SSAS - No of Working Days as Named Calculation

Question

I am new to SSAS. I have a requirement, I need to calculate no of working days between user selected date range (either in Excel or SSRS or PowerBI). I found the MDX query, I need assistance with create a named calculation with MDX expression.

Date Dimension (Filtered):

MDX:

WITH MEMBER Measures.WorkingDays AS 
COUNT  
(
exists( EXISTING {[Dim Date].[Date].[Date].members}
, [Dim Date].[Is Weekday].&[1] )
) 

Select {Measures.WorkingDays} on 0 , 
[Dim Date].[Month].[Month] on 1 
from [Project Cube]
where ([Dim Date].[Date].&[2018-01-01T00:00:00]:[Dim Date].[Date].&[2018-04-25T00:00:00])

I need to add this named column on Fact table as measurement. I am having trouble with the below items:

Creating named query with MDX expression mentioned.
Adding a [Number of Working Days] as measure in Fact table.

Please correct me, If I am doing it in wrong way. My requirement is I need a [NoOfWorkingDays] as measure in fact table, so that I can use SSAS aggregate to use it as input on other measure, such as ([utilization%] = ([ActualDaysWorked] / [NoofWorkingDays]).

Note that, I can do analysis with the given MDX, but I need to deploy it with precalculated values in cube, so that end user can directly use the cube.

Kindly let me know, if more details required, Thank you.

MoazRub MoazRub · Accepted Answer · 2019-01-24T13:08:57

Welcome to SSAS and MDX. Now to the answer.

I need to add this named column on Fact table as measurement. I am having trouble with the below items:

Creating named query with MDX expression mentioned. Adding a [Number of Working Days] as measure in Fact table.

You dont need to add it to the Fact table at all. Open your SSAS project, in your object explorer double click your cube. Now on the top left hand you will see a CALCULATIONS tab. In the CALCULATION tab, Click new calculated member, the icon has a calculator on it.

Please correct me, If I am doing it in wrong way. My requirement is I need a [NoOfWorkingDays] as measure in fact table, so that I can use SSAS aggregate to use it as input on other measure, such as ([utilization%] = ([ActualDaysWorked] / [NoofWorkingDays]).

If I remember correctly, the calculated members will not be added into the Aggregations, however the underlying measures would be. Secondly if you are wondering that you can use your calculated Measure in another calculated measure. The answer is yes you can use it in another calculated measure. So this is totally possible

> ([utilization%] = ([ActualDaysWorked] / [NoofWorkingDays])

where [utilization%] and [NoofWorkingDays] are calculated measures.

SSAS - No of Working Days as Named Calculation

1 Answers