Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Calculated Member Question

2 posters

Go down

Calculated Member Question Empty Calculated Member Question

Post  cjrinpdx 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


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

Back to top Go down

Calculated Member Question Empty Re: Calculated Member Question

Post  Boyan Penev 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
{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
Exists(Existing [Ticket].[Ticket].[Ticket],
"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

Back to top Go down

Back to top

- Similar topics

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