[SOLVED] Mondrian MDX - Filter Not Applying to Multiple Members Query

Mondrian MDX - Filter Not Applying to Multiple Members Query

We currently have a query that shows the value of total quantity, or sales across store locations and departments, I intersect the quantity sold aggregated measure against locations, and the results are fine.

When we try to filter by total qty > 500, I am only seeing the sum of quantity evaluated by the location's value, not grouped by location and department.

HAVING is not seemingly supported, and if I include a where filter at the bottom, there is an issue with the same family/members being used multiple times and it's not allowed.

SELECT 
  {HEAD([dim_productfamily.hier_productfamily].[lvl_department].Members, 5)}
ON ROWS,
  FILTER
  (
    {HEAD([dim_locations.hier_locations].[lvl_location].Members, 5) * [Measures].[total_qty]},
    [Measures].[total_qty] > 500
  )
ON COLUMNS
FROM 
  [sales_daily] 
WHERE 
  {[dim_date.hier_date].[lvl_date].[20170521] : [dim_date.hier_date].[lvl_date].[20170730] }

The above query returns fine, but I get values that I've tested only really compare against the location sum(total_qty).

EDIT for Different Grouping

I tried using the below query, which seems to work. I think the way we render a table is improper in this case, as the output does seem to work fine.

SELECT 
  FILTER
  (
     {HEAD([dim_productfamily.hier_productfamily].[lvl_department].Members, 5) * HEAD([dim_locations.hier_locations].[lvl_location].Members, 5)},
     [Measures].[total_qty] > 26
  )
ON ROWS,
  [Measures].[total_qty]
ON COLUMNS
FROM 
   [sales_daily] 
WHERE 
  {[dim_date.hier_date].[lvl_date].[20170521] : [dim_date.hier_date].[lvl_date].[20170730] }

Is this what you were thinking?

Solution

The issue is that in your filter you really have not grouped by location and department. You have only grouped by location. An easy fix is the you bring both location and product on the same axis and that the measure on the oposite axis. Then filter it. It would work.