WITHOUT PARENT FILTER EXCEPT

Remove parent filters, except for any filters specified as a comma-separated list of attributes.

This clause can be shortened to WITHOUT PF EXCEPT.

In EXCEPT clauses, multiple attributes may be specified as a series of comma-separated values.

Excepted attribute filters are applied to the specified attribute and to all attributes of coarser granularity in the hierarchy. For example, in the hierarchy State-County-City, the values of the attribute City do not group the values of County, but County groups City because County has coarser granularity than City. So, if City is excepted, County and State are excepted too. If you apply the exception to the State attribute, it is the only attribute filter that is excepted.

Syntax

SELECT ... WITHOUT PARENT FILTER EXCEPT Attribute1,...
SELECT ... WITHOUT PF EXCEPT Attribute1,...

Example 1

In the table below, you can see how the WITHOUT PARENT FILTER EXCEPT keyword works with the State-County-City hierarchy.

Definitionvisualization FilterMetric Value
SELECT M1(none)1000
SELECT M1F1: County = X600
SELECT M1 WITHOUT PFF1: County = X1000
SELECT M1 WITHOUT PF EXCEPT CityF1: County = X600
SELECT M1 WITHOUT PF EXCEPT StateF1: County = X1000
SELECT M1 WITHOUT PF EXCEPT StateF1: State = Y550
  • In the above example in the third row, the WITHOUT PARENT FILTER removes all parent filters from the metric, so COUNTY = X is not applied. The returned results match the unfiltered version of the metric in the first row.
  • In the fourth row, City is excepted. Since County has a coarser grain than City, the parent visualization filter (County = X) is applied.
  • In the fifth row, the excepted filter is State. However, the visualization filter is at the County attribute level, which is finer than State. Therefore, the filter is not applied, and the results match the unfiltered version in the first row.
  • In the fifth row, the excepted filter is State, which matches the visualization filter. So, the results are filtered.

Example 2

In the table below, you can see how the WITHOUT PARENT FILTER EXCEPT keyword affects the filtered version of the visualization.

DefinitionVisualization FilterMetric Value
SELECT M1(none)500
SELECT M1F1: Group = X350
SELECT M1F1: Group =250
X; F2: Priority is Y
SELECT M1 WITHOUT PARENT FILTER EXCEPT GroupF1: Group = X; F2: Priority is Y350

In the above example in the fourth row, the WITHOUT PARENT FILTER EXCEPT removes all parent filters from the metric, except for one: Group, so the Priority filter is not applied to the metric. The generated metric value equates to the value generated in the version in the second row.

Example 3

Definitionvisualization Filter
SELECT (SELECT M1 WITHOUT PF EXCEPT Date) WHERE (SELECT M2 BY Date) > YGroup = X
  • M2 is filtered by group.
  • M1 is not filtered by group, only by the numeric range filter in the WHERE part.

Example 4

DefinitionVisualization Filter
SELECT (SELECT M1 WITHOUT PF EXCEPT Date) WHERE (SELECT M2 BY Date) > YGroup = X Date = Z
  • M2 is filtered by group, date.
  • M1 is filtered by date and the numeric range in the WHERE part.