Hi
I am struggling with the syntax for "within set"
I have a table of test cases, each test case can have multiple results. Each result also has a run date.
For each test case, I want to select ONLY the most recent result
Test Case Result Run Date
TC1 pass 1 Jan 2014
TC1 fail 2 Jan 2014
TC1 pass 11 Jan 2014
TC2 fail 1 Jan 2014
TC2 pass 9 Jan 2014
should give
Test Case Result Run Date
TC1 pass 11 Jan 2014
TC2 pass 9 Jan 2014
I have been trying
maximum( [TestCasePerspectiveSummary].[End Date] within set [TestCasePerspectiveSummary].[Test Case Name] )
but get OP-ERR-0061
Filter expressions must evaluate to Boolean. Error processing filter 'maximum( [__ns_0].[__detail_dim].[__detail_dim].[__detail_level].[End Date] within set [__ns_0].[__detail_dim].[__detail_dim].[__detail_level].[Test Case Name] )'.
Is the correct approach but something swry with the syntax or is there an easier way to do this?
Thanks
There are a few ways of doing this, but it depends on what type of data source you're using. The within set method is generally only used against dimensional sources. In your case I suspect this is a relational source.
Try the filter: [Run Date] = maximum([Run Date] for [Test Case])
Quick edit: A data warehouse should be built to the needs and specifications of the analyses needed. It looks like you're referencing a slowly changing dimension. I strongly recommend adding a new field that flags the most recent row for each test case. Once you have that the filter would be an easier, and far more efficient, [Most Recent] = 'Yes'.
Hi Paul
Thanks so much! This works perfectly.
Una ;D