Exclude query filters dynamically in data schema measures

Hello!

Does anyone know if it’s possible to exclude query filters dynamically in data schema measures?

At the moment we have static filters in some measures to get certain count with specific logic, like this:

cube(`Lookalikes`, {
  sql: `SELECT * FROM "Lookalikes"`,
  measures: {
    countSomething: {
      sql: `${CUBE}."id"`,
      type: `countDistinct`,
      filters: [
        {
          sql: `${CUBE.LookalikeSourceId} = 1`,
        },
      ],
      shown: false,
    },
  }
});

But what we would like to achieve is something like:

// If the used query includes filters like this
[
  { "member":"LookalikeSource.name", "operator":"equals", "values":["LookalikeSource"]},
  { "member":"Departments.name", "operator":"equals", "values":["Department1", "Department2", "Department3"]}
]

// and data schema is something like this

cube(`Lookalikes`, {
  sql: `SELECT * FROM "Lookalikes"`,
  measures: {
    uniqueCountWithoutLookalikeSourceFilter: {
      sql: `${CUBE}."id"`,
      type: `countDistinct`,
      filters: ${IMAGINARY_QUERY_FILTER_PARAMS_FROM_QUERY.filter(filterData => filterData.member !== "LookalikeSource.name")}, // THIS IS THE "MAGIC" WE NEED
      shown: false,
    },
    uniqueCount: {
      sql: `${CUBE}."id"`,
      type: `countDistinct`,
      shown: false,
    },
    countDifferenceBetweenLookalikesWithoutLookalikeSourceAndUniqueCount: {
      sql: `${CUBE.uniqueCountWithoutLookalikeSourceFilter} - ${CUBE.uniqueCount}`,
      type: `number`,
      shown: false,
    },
  }
});

So in that case we don’t want to use "LookalikeSource.name" filter in that uniqueCountWithoutLookalikeSourceFilter measure if it’s included in the query.filters but any other filters will be used and uniqueCount measure will use all filters in the query. And that countDifferenceBetweenLookalikesWithoutLookalikeSourceAndUniqueCount measure will be “0” if "LookalikeSource.name" isn’t used in the query.

Is that possible to do somehow?

The problem from database point of view is:

# Basic unit in this case is "Texts" that we want to count
# One Text have >=1 "Versions"
# Versions can have multiple "Lookalikes"
# Lookalikes have "LookalikeSource"

Versions +--1 Texts
Lookalikes *--1 Versions
Lookalikes *--1 LookalikeSources

# "Texts" and "Versions" of them are published every day
# "Lookalikes" are found for "Versions" every day from different "LookalikeSources"
# We want to calculate how many "Texts" are published (for example) yesterday that don't have any "Lookalikes" from "LookalikeSource X" or "LookalikeSource Y" etc.

So if user want’s to calculate “zero lookalikes texts” Texts.count from “LookalikesSource X” I think we need to calculate how many Texts are published yesterday ( measure 1 ) , then we need to calculate how many Texts have Lookalike -match from “LookalikesSource X” ( measure 2 ) and the difference is our result => measure 3 = measure 1 - measure 2. BUT in that case the query from frontend includes filter

{
member: 'LookalikeSources.name',
operator: 'equals',
values: ['LookalikeSource X'],
},

and we don’t want to use it when calculating “how many Texts are published yesterday”. ( measure 1 ). So is there any way to exclude certain filter in case like this when using only one query? Data blending could work because then we can run multiple queries ( measure 1 and measure 2 separately) and exclude the certain filter from measure 1 but in that case we need to calculate the result in frontend :frowning: