Power pivot named MDX field

Question

I have a PowerPivot Data Model in Excel 2013. There are several measures that I have grouped into a named set using MDX - something like this:

{[Measures].[Sum of Value1],
[Measures].[Sum of Value2],
[Measures].[Sum of Value3]}

By using this named Set, I can place multiple measures on the rows or columns of an Excel PivotTable in a single action. My question is, is there any way using MDX (or DAX in the PowerPivot screen when working with the individual measures) to filter out or hide the entire set based on a single measure value (whether that measure is included in the set or not)? Preferably, I'm looking for a way to do this without including another member in the set (I.e. Not a measure).

Ror example, if the Sum of Value3 in the above example was zero, I'd want the entire set to be hidden from the pivot table.

I know I could edit the DAX in the Data Model to return BLANK() for each measure included in the set based on the value of another measure, but there may be times I want to show those measures in all cases. This would require writing at least 2 measures for every one I have now which I don't like the thought of doing.

UPDATE:

Sourav's answer looks great, but unfortunately won't work in my particular scenario, I believe, because I'm using the "Create Set using MDX" function (under the Manage Sets option in the Fields, Items, & Sets ribbon menu) within Excel. It will only let me write the MDX as:

IIF([Measures].[Sum of Value3]=0,
{},
{[Measures].[Sum of Value1],[Measures].[Sum of Value2],[Measures].[Sum of Value3]})

And once I add that new set to the PivotTable, it will still display all 3 measures for any members where [Sum of Value3] is 0.

I think I'm going to have to find an approach using DAX and the Excel Data Model measures.

UPDATE 2:

Below is a screenshot to help illustrate. Keep in mind the data source in my example is not an external cube, it's simply an Excel file linked in the Data Model against which MDX queries (with limitations?) can be run. In this example, I would like the set to return only Rows A and C because Sum of Value3 is not zero. However, as you can see, all rows are being returned. Thanks!

Answer 1

You can't choose to hide/unhide members/sets on the fly. Instead, you can use IIF to conditionally return an empty set

WITH SET MyNamedSet AS
IIF([Measures].[Sum of Value3] = 0, 
{}, 
{[Measures].[Sum of Value1],[Measures].[Sum of Value2], [Measures].[Sum of Value3]}

Working example in AdventureWorks for @whytheq(DISCLAIMER - Cube was created by me for testing purposes)

with set abc as
iif([Measures].[Fact Internet Sales Count]>34229, 
    {
    [Measures].[Fact Internet Sales Count],
    [Measures].[Extended Amount - Fact Internet Sales]
    },
    {}
    )

SELECT 
abc
 on 0
from [AdventureWorksDW]
where [Due Date].[Year].&[2004]

As you can see, the scope IS changing the results.

Answer 2

An alternative would be to create a dummy measure that returns null or 1 depending on your [Measures].[Sum of Value3]. Then multiply all other target measures by this dummy measure.

Here is an example of you scenario in AdvWrks:

SELECT 
  [Product].[Product Categories].[Category].[Components] ON 0
 ,{
    [Measures].[Internet Sales Amount]
   ,[Measures].[Sales Amount]
   ,[Measures].[Standard Product Cost]
   ,[Measures].[Total Product Cost]
  } ON 1
FROM [Adventure Works];

Returns this:

Adding the dummy measure and amending the other measures:

WITH 
  MEMBER [Measures].[isItZero] AS 
    IIF
    (
      [Measures].[Internet Sales Amount] = 0
     ,null
     ,1
    ) 
  MEMBER [Measures].[Sales Amount NEW] AS 
    [Measures].[Sales Amount] * [Measures].[isItZero] 
  MEMBER [Measures].[Standard Product Cost NEW] AS 
    [Measures].[Standard Product Cost] * [Measures].[isItZero] 
  MEMBER [Measures].[Total Product Cost NEW] AS 
    [Measures].[Total Product Cost] * [Measures].[isItZero] 
SELECT 
  NON EMPTY //<<<<this is required
    {
      [Measures].[Internet Sales Amount]
     ,[Measures].[Sales Amount NEW]
     ,[Measures].[Standard Product Cost NEW]
     ,[Measures].[Total Product Cost NEW]
    } ON 0
 ,{} ON 1
FROM [Adventure Works]
WHERE 
  [Product].[Product Categories].[Category].[Components];

Now this returns:

---

EDIT

According to your latest edit please just try this (I'm assuming you're using Excel 2013):

Create two new measures to replace two of the existing ones:

Name: "Sum of Value1 NEW" Definition:

   IIF
    (
      [Measures].[Sum of Value3] = 0
     ,null
     ,[Measures].[Sum of Value1]
    ) 

Name: "Sum of Value2 NEW" Definition:

   IIF
    (
      [Measures].[Sum of Value3] = 0
     ,null
     ,[Measures].[Sum of Value2]
    ) 

Now use only these three measures in your pivot and just use the ID dimension in a normal way on rows i.e. do not use the custom set you have already tried.

[Measures].[Sum of Value1 NEW]
[Measures].[Sum of Value2 NEW]
[Measures].[Sum of Value3]

Has ID B should now disappear?