Some help with filtering

Questions & Answers
1

Hello friends! Clueless Alex again looking for your kind help :slight_smile:

I have a Measure ([Measure].[Issues with dev completed]) and I want to apply a filter on this measure. Specifically, I want to exclude those issues that have the the Ready for Demo flag marked as Yes (i.e. [Ready for Demo].[Yes]).

Likewise, I have a measure that brings back all issues with [Status].[In Progress], but I want to exclude issues that have Dev Complete <> blank, UAT Completed <> blank, Ready for demo <> Yes.

There are other scenarios, but they broadly fit the above scenario where I’d like to filter one element based on the value (or not) of another


2

Ok, so I think I’ve solved it for the filtering across dimensions:

SUM(Filter(
– this will filter out issues with Ready for Demo = yes
Descendants([Ready for Demo].CurrentMember,[Ready for Demo].[Ready for Demo]),
[Ready for Demo].CurrentMember.Name <> “Yes”),
– this measure in the last SUM works as a fitler and as counter
[Measures].[Issues with uat started])
)

Now
I want to filter out something that isn’t a unique Dimension, but is a property on the Issue (specifically, the UAT Completed date). For the below, I want to filter out any issues that have a UAT Completed date populated (it’s a date field). The following doesn’t seem to work:

SUM(Filter(
– this will filter out issues with Ready for Demo - yes
Descendants([Ready for Demo].CurrentMember,[Ready for Demo].[Ready for Demo]),
[Ready for Demo].CurrentMember.Name <> “Yes”),
SUM(Filter(Descendants([Issue].CurrentMember, [Issue].[Issue]),
[Issue].CurrentHierarchyMember.get(‘UAT Started’) <> (‘none’)),
– this measure in the last SUM works as a filter and as counter
[Measures].[Issues with dev completed])
)

3

Ok, I think I’ve cracked it for the second query I was trying to figure out
can a ‘guru’ on here validate the below is actually correct (and not just luck!):

SUM(Filter(
– this will filter out issues with Ready for demo = yes
Descendants([Ready for Demo].CurrentMember,[Ready for Demo].[Ready for Demo]),
[Ready for Demo].CurrentMember.Name <> “Yes”),
– this will filter out issues where Dev Completed is empty
SUM(Filter(Descendants([Issue].CurrentMember,[Issue].[Issue]),
isEmpty([Issue].CurrentMember.get(‘Dev Completed’))),
– this measure in the last SUM works as a fitler and as counter
[Status].[In Progress]))

Thoughts?

4

Hi,

In this case, you could create a calculated member in “Ready for Demo” dimension using aggregate except

Aggregate( 
	Except( 
	[Ready for Demo].[Ready for Demo].Members, 
	{ 
		[Ready for Demo].[Yes]
	}) 
)

Then you could create a calculated measure using a tuple (where you use the calculated member from previous step)

(
[Measures].[Issues with dev completed],
[Ready for Demo].[New calculated member]
)

The same principle also for other dimensions.

Martins / eazyBI support