How to filter data in query with multiple CROSSJOINs by a flag value

Question

I have a query with a multiple CROSSJOIN on rows and columns I want to filter all data [Measures].[Flag] = 1 Here is an example:

SELECT 
  {
    NonEmpty
    (
      CrossJoin
      (
        {
          [Time].[2016]
         ,[Time].[2017]
        }
       ,CrossJoin
        (
          {
            [Quarters].[2 Quarter]
           ,[Quarters].[1 Quarter]
          }
         ,{
            [Measures].[Load]
           ,[Measures].[Flag]
          }
        )
      )
    )
  } ON ROWS
 ,{CrossJoin([Industry].[Industry 1],[Client].[Set 1])} ON COLUMNS
FROM [Cube]
WHERE 
  [Version].[Actual];

If I do something like this:

SELECT 
  {
    NonEmpty
    (
      CrossJoin
      (
        {
          [Time].[2016]
         ,[Time].[2017]
        }
       ,CrossJoin
        (
          {
            [Quarters].[2 Quarter]
           ,[Quarters].[1 Quarter]
          }
         ,{
            [Measures].[Load]
           ,[Measures].[Flag]
          }
        )
      )
    )
  } ON ROWS
 ,{
    Filter
    (
      CrossJoin
      (
        [Industry].[Industry 1]
       ,[CLient].[Set 1]
      )
     ,
      [Measures].[Flag] = 1
    )
  } ON COLUMNS
FROM [Cube]
WHERE 
  [Version].[Actual];

I get an empty set. In a result set witout a filter there is data with Flag = 1

Answer 1

Hi I have found a solution to this problem. The fix is to use filter differently. Here is an example:

The big thing to notice here was that 'Flag' data was available in specific dimension configuration.

SELECT 
  {
    NonEmpty
    (
      CrossJoin
      (
        {
          [Time].[2016]
         ,[Time].[2017]
        }
       ,CrossJoin
        (
          {
            [Quarters].[2 Quarter]
           ,[Quarters].[1 Quarter]
          }
         ,{
            [Measures].[Load]
           ,[Measures].[Flag]
          }
        )
      )
    )
  } ON ROWS
 ,{
    Filter
    (
      CrossJoin
      (
        [Industry].[Industry 1]
       ,[CLient].[Set 1]
      )
     ,
      (
       [Cube].(
               [Time].[2016],
               [Quarters].[1 Quarter],
               [Measures].[Flag]
              ) = 1
      )
    )
  } ON COLUMNS
FROM [Cube]
WHERE 
  [Version].[Actual];

Answer 2

If you just move it into a HAVING clause does that help?

SELECT 
  {
    NonEmpty
    (
      CrossJoin
      (
        {
          [Time].[2016]
         ,[Time].[2017]
        }
       ,CrossJoin
        (
          {
            [Quarters].[2 Quarter]
           ,[Quarters].[1 Quarter]
          }
         ,{
            [Measures].[Load]
           ,[Measures].[Flag]
          }
        )
      )
    )
  } ON ROWS
 ,
      CrossJoin
      (
        [Industry].[Industry 1]
       ,[CLient].[Set 1]
      )
     having [Measures].[Flag] = 1 ON COLUMNS
FROM [Cube]
WHERE 
  [Version].[Actual];