Calculated Member Question

[cjrinpdx]

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

[Boyan Penev]

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.