How to create a calculated member from a dimension attribute (visual studio BI studio)


  • So I am working on in Visual Studio 2010 BI studio and I am trying to create a calculated member from a dimension attribute.

    The attribute is an int that retains values 1-3.  The following is how the cube fact/dimensions tables are setup:

    Fact table:

    Table: Assignments userid bigint statusid bigint

    Hierarchy {Full Name: {[Last name], [First Name], [id]}}


    Table: End User Id name

    Hierarchy {User: {[End User - id]}}

    We have also created an dimension of this table because we want to slice on it.

    I am trying to create a calculated member so that I can show on the Y-axis the users and the X-axis the calculated member where [Assignments].[status id] = 3.

    The [End User] dimension has a relation to the [Assignments] fact table through [Assignments].[user id] -> [End User].[id].    I want to slice on the End User and show the count of where [Assignments].[status id] = 3 as a calculated measure.

    I have tried a number of ways, but basically, trying to interate through each member of the [Assignemnts].[User] and where statusid = 3 and sum 

    SUM(FILTER(DESCENDANTS([Fact Assignment User].[User].currentmember, 1), [Fact Assignment User].[Status Id] = 3))

    But I am not sure how to pull this off since:

     - The dimension is of the fact table itself.

     - Trying to create calculated member of a dimension attribute.

    Any help?

    Dienstag, 9. April 2013 14:25

Alle Antworten