Calculated Member Question
2 posters
Page 1 of 1
Calculated Member Question
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
Re: Calculated Member Question
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.
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.
Similar topics
» Capturing Age at point in time - SCD2 / Calculated Member/ New Dimension??
» Member - Member Account Physical Representation
» dimension table design question for around 100 attributes and higher level calculated attributes
» Any Member with experience in QlikView
» Member eligibility dimension / fact
» Member - Member Account Physical Representation
» dimension table design question for around 100 attributes and higher level calculated attributes
» Any Member with experience in QlikView
» Member eligibility dimension / fact
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
|
|