none
MDX calculated measure with parameters within dataset RRS feed

  • Question

  • Need to add a second parameter to this existing dataset. There are filters in place to make sure the are a minimum number of users included in the sum. I need to make sure the second parameter is included in the distinct count since the amount of users will go down when the new parameter is applied.

    I have tried a few different things to add in the parameter with no success. 

    Parameters Examples: @Organization (first param) and @Tenure (second param)

    WITH MEMBER [Measures].[SumEntries] AS

         SUM(FILTER(STRTOSET(@Organization),DISTINCTCOUNT(DESCENDANTS(STRTOSET(@Organization),,SELF_AND_AFTER)) >= 5), [Measure].[Entries])

    <[MEMBER] ... >

    <[MEMBER] ... >

    <[MEMBER] ... >

    FROM [ABC]

    Thursday, March 19, 2015 12:37 PM

All replies

  • Hi,

    Need more Info

    a) Is your First parameter is working fine in your MDX, before adding the second parameter?

    b) Second Parameter on which Dimension--> Attribute you want to apply?

    c) If you dont want to show your Dimesnions Names  to us as in MDX, you can seply replace your actual dimensions names to -- DimA and DimB so on. So we can get the structure of your MDX.

    Thanks

    Prasad

    Thursday, March 19, 2015 12:53 PM
  • The measure above works and has been working for a while now. I am using the second parameter with no issue on other reports for a different purpose.

    The real question I am trying to answer is how should the calculated measure be rewritten in order to first filter the result by the @Tenure and then do a distinct count of @Organization.

    So if I am logging in as UserA and I have 10 people below me, 5 of them are less than 5 years and 5 are greater than 5 year. The current calculation just says that I am UserA with a distinct count of 10. I want to apply my filter for example, of people that have been here less than 5 years and I would get a distinct count back of 5.

    Thursday, March 19, 2015 1:08 PM
  • The measure above works and has been working for a while now. I am using the second parameter with no issue on other reports for a different purpose.

    The real question I am trying to answer is how should the calculated measure be rewritten in order to first filter the result by the @Tenure and then do a distinct count of @Organization.

    So if I am logging in as UserA and I have 10 people below me, 5 of them are less than 5 years and 5 are greater than 5 year. The current calculation just says that I am UserA with a distinct count of 10. I want to apply my filter for example, of people that have been here less than 5 years and I would get a distinct count back of 5.

    having trouble following your example (organization/tenure). Can you provide more detail like Prasad requested?

    If it really is as simple as finding the number of people below a specific person in the organization (e.g. employee parent child hierarchy) that meet a certain criteria (e.g. greater than 5 years) then you could use the below example based on AdventureWorks...

    WITH
    	MEMBER [Measures].[FilteredReports] AS
    		FILTER(
    			DESCENDANTS(
    				[Employee].[Employees].CurrentMember
    				,
    				,AFTER
    			)
    			,[Measures].[Reseller Sales Amount] > 5000000
    		).Count
    
    SELECT	
    	{
    		[Measures].[FilteredReports] // Number of Reports w/ ResellerSales > 5mm
    	} ON 0,
    	{
    		{
    			 [Employee].[Employees].&[290] // Amy E. Alberts
    			,[Employee].[Employees].&[272] // Stephen Y. Jiang
    			,[Employee].[Employees].&[294] // Syed E. Abbas
    		}
    	} ON 1
    FROM	
    	[Adventure Works]
    ;

    This calculated member returns the number of reports with reseller sales > $5mm.

    Perhaps this translates to your problem where the $5mm in the filter clause would be the @Tenure parameter, and you'd replace the [Employee].[Employees].CurrentMember with the STRTOSET(@Organization)...

    WITH
    	SET [Organization] AS
    		{
    			 [Employee].[Employees].&[290] // Amy E. Alberts
    			,[Employee].[Employees].&[272] // Stephen Y. Jiang
    			,[Employee].[Employees].&[294] // Syed E. Abbas
    		}
    	MEMBER [Measures].[Tenure] AS
    		5000000
    
    	MEMBER [Measures].[FilteredReports] AS
    		FILTER(
    			DESCENDANTS(
    				[Organization]
    				,
    				,AFTER
    			)
    			,[Measures].[Reseller Sales Amount] > [Measures].[Tenure]
    		).Count
    
    SELECT	
    	{
    		[Measures].[FilteredReports] // Number of Reports w/ ResellerSales > 5mm
    	} ON 0
    FROM	
    	[Adventure Works]
    ;

    ...which probably becomes...

    WITH
    	MEMBER [Measures].[Tenure] AS @Tenure
    
    	MEMBER [Measures].[YearsOfService] AS 
    		DateDiff(
    			"yyyy"
    			,[Employee].[Employees].CurrentMember.Properties( "Start Date" )
    			,Now()
    		)
    	MEMBER [Measures].[FilteredReports] AS
    		FILTER(
    			DESCENDANTS(
    				STRTOSET(@Organization)
    				,
    				,AFTER
    			)
    			,[Measures].[YearsOfService] >= [Measures].[Tenure]
    		).Count
    
    SELECT	
    	{
    		[Measures].[FilteredReports] // Number of Reports w/ ResellerSales > 5mm
    	} ON 0
    FROM	
    	[Adventure Works]
    ;



    BI Developer and lover of data (Blog | Twitter)


    • Edited by BillAnton Thursday, March 19, 2015 7:58 PM
    Thursday, March 19, 2015 7:57 PM
  • Thanks for all the great information. I still think this over-complicates the solution though.

    So the calculated measure I am modifying already works. I have an app with an RS report embedded. When I log into the app, it already knows I am UserXXXX. I have the option to filter down to a subordinate. If I do that then my distinct count changes which is expected.

    I have an additional parameter that I would like to include as a filter. So when I log in, the @tenure parameter is defaulted to all which I already took care of. There is no calculation required for @tenure. I already have stored values for it which include: 1-2 Years, 2-5 Year, 5-10 Years, More than 10 Years. These values are already set for each user. I just need to add @tenure as a filter in the calculated member. So as an example, in the expression of the filter would I do something like, [Tenure Type].[Tenure].CurrentMember = @Tenure.



    • Edited by MP4SPO Friday, March 20, 2015 11:48 AM
    Friday, March 20, 2015 11:38 AM
  • So does @Organization represent you (UserXXXX)? Or a subordinate, if you choose to filter down? Or does (can) it represent multiple people (of varying levels in the hierarchy)? 

    Is Tenure a separate dimension? 

    SUM(
    	FILTER(
    		 STRTOSET(@Organization)
    		,DISTINCTCOUNT(
    			DESCENDANTS(
    				 STRTOSET(@Organization)
    				,
    				,SELF_AND_AFTER
    			)
    		) >= 5
    	)
    	,[Measure].[Entries]
    )

    This currently "reads": give me the total "entries" for those members in @Organization that have 4 or more children (because you used SELF_AND_AFTER instead of AFTER the parent member is included).

    And you want it to "read": give me the total "entries" for those members in @Organization that have 4 or more children who in turn meet certain tenure criteria (e.g. 1-2 Years).

    Is that correct?


    BI Developer and lover of data (Blog | Twitter)

    Friday, March 20, 2015 12:04 PM
  • So does @Organization represent you (UserXXXX)? Yes

    Or a subordinate, if you choose to filter down? Yes, so it will only do a distinct count of the children for that subordinate if I filter below me.

    Or does (can) it represent multiple people (of varying levels in the hierarchy)? No, just a count of the people below a single user.

    Is Tenure a separate dimension? Yes

    And you want it to "read": give me the total "entries" for those members in @Organization that have 4 or more children who in turn meet certain tenure criteria (e.g. 1-2 Years).

    Is that correct? Exactly

    Friday, March 20, 2015 12:10 PM
  • k - so how is Tenure related to Organization? 

    reference dimension? measure group?

    if through a measure group, I'm assuming that was because you want to track tenure-status over time...so older facts for a single person in the organization are lower on the tenure status (e.g. 1-2 years) and more recent facts for this same person are higher on the tenure status (e.g. 5-10 years). if this is the case, and the fact table through which organization and tenure are related is [measures].[entries], then do you want the tenure-filter to also apply to the outer SUM of [measures].[entries]?


    BI Developer and lover of data (Blog | Twitter)

    Friday, March 20, 2015 12:21 PM
  • Do you mind if I email you the structure? I would rather not post it to the forum.
    Friday, March 20, 2015 7:15 PM
  • Do you mind if I email you the structure? I would rather not post it to the forum.
    No problem...totally understand.

    BI Developer and lover of data (Blog | Twitter)

    Friday, March 20, 2015 7:49 PM
  • Should I email your blog email or another preferred email? 
    Friday, March 20, 2015 8:01 PM
  • email on blog will work ;-)

    BI Developer and lover of data (Blog | Twitter)

    Sunday, March 22, 2015 8:56 PM