Calculated Member Question

View previous topic View next topic Go down

Calculated Member Question

Post  cjrinpdx on Mon Oct 03, 2011 5:47 pm

I have a factless fact table called Tickets (TicketKey, TicketDateKey, EmployeeKey) that represents the many to many relationship between tickets and employees. Tickets are related to ZERO or more employees. I want to create a calculated member that is a distinct count of TicketKey, where the EmployeeKey is not empty. Can anyone help me with the MDX? Thanks

cjrinpdx

Posts : 51
Join date : 2011-07-14
Location : Portland, OR

View user profile

Back to top Go down

Re: Calculated Member Question

Post  Boyan Penev on Mon Oct 03, 2011 8:58 pm

I am assuming you have a Ticket dimension, an Employee dimension, a Date dimension and a row-bound count measure over you factless fact table. Essentially, you need to find the distinct count of Ticket leaf members which have a not-Unknown Employee in the table. That is if you insert a new rows with TicketKey and EmployeeKey of -1 (for Unknown).

CREATE MEMBER [Measures].[Distinct Ticket Count] AS
DistinctCount(
Exists([Ticket].[Ticket].[Ticket],
{Employee].[Employee].[Employee]-[Employee].[Employee].&[-1]}, --eg all employees other than the Unknown one
"Measure Group Name" --replace with the name of the measure group based on your fact table
)
);

If you want this to take into account the slicer for Tickets (e.g. Tickets existing within a particular time period placed in the WHERE clause):

CREATE MEMBER [Measures].[Distinct Ticket Count] AS
DistinctCount(
Exists(Existing [Ticket].[Ticket].[Ticket],
{Employee].[Employee].[Employee]-[Employee].[Employee].&[-1]},
"Measure Group Name"
)
);

If you have a SSAS Unknown member, then things are a bit different and you'd need to replace [Employee].[Employee].&[-1] with [Employee].[Employee].UnknownMember.

Boyan Penev

Posts : 4
Join date : 2011-10-03
Location : Melbourne, Australia

View user profile http://www.bp-msbi.com

Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum