I need to filter this MDX result set

Question

I am looking to filter the result set below such that I only show results where Dimension A Value 1 have a count of 1, regardless of the value of count for Dimension A Value 2

           Dimension A Value 1     Dimension A Value 2
Entity ID   Count                Count
11              1   
78          1   
90          1   
101         1   
114                                1
118         1   
125         1   
134                                    1
140         1   
161         1   
169         1   
186         1                  2

The filtered set would look like

    Dimension A Value 1 Dimension A Value 2
Entity ID   Count               Count
11           1  
78           1  
90           1  
101          1  
118          1  
125          1  
140          1  
161          1  
169          1  
186          1                 2

the mdx is

WITH
SET [~COLUMNS] AS
    {[Dimension A].[Dimension A].[Value 1], [Dimension A].[Dimension A].[Value 2]}
SET [~ROWS] AS
    {[Entity].[Entity].[Entity ID].Members}
SELECT
NON EMPTY CrossJoin([~COLUMNS], {[Measures].[Count]}) ON COLUMNS,
NON EMPTY [~ROWS] ON ROWS
FROM [My Cube]

I've been playing around with Filter and NonEmpty but I'm new to MDX and my sql brain is hurting. I suppose this is probably trivial to someone with a lot of MDX under their belt but I'm failing. Be gentle this is my first question

Answer 1

You can try a HAVING clause:

WITH
SET [~COLUMNS] AS
    {
      [Dimension A].[Dimension A].[Value 1], 
      [Dimension A].[Dimension A].[Value 2]
    } 
MEMBER [Measures].[CountValue1] AS   //<<<<this is new <<<<<<<<<<<<<<<<<<<<<<<<<<<<<
    (
       [Measures].[Count],
       [Dimension A].[Dimension A].[Value 1]
    )
SELECT
NON EMPTY 
  CrossJoin(
    [~COLUMNS]
  , {[Measures].[Count]}
  ) ON COLUMNS,
NON EMPTY 
  [Entity].[Entity].[Entity ID].MEMBERS 
  HAVING [Measures].[CountValue1] = 1    //<<CHANGED TO NEW MEASURE
  ON ROWS
FROM [My Cube];

If you can use HAVING rather than FILTER you will likely see a performance improvement - particularly if your scripts become more complex:
https://blog.crossjoin.co.uk/2006/01/04/the-having-clause/

Just to be complete the slower FILTER version would be this:

WITH
SET [~COLUMNS] AS
    {
      [Dimension A].[Dimension A].[Value 1], 
      [Dimension A].[Dimension A].[Value 2]
    } 
//>>>>>> following is new >>>>>>>>>>>>>>>>>>>>>
MEMBER [Measures].[CountValueNEW] AS   
    (
       [Measures].[Count],
       [Dimension A].[Dimension A].[Value 1]
    )
SELECT
NON EMPTY 
    [~COLUMNS]
   *{[Measures].[Count]}
ON 0,
NON EMPTY 
FILTER(
  [Entity].[Entity].[Entity ID].MEMBERS,
  [Measures].[CountValueNEW] = 1 
)  
ON 1
FROM [My Cube];