Make selection without altering pivot table layout

[Anonymous]

I know the native response  is that if you have a dimension with four values in a pivot table and a filter is applied to select one of them, the entire pivot will change to only show that value within that dimension.  Is there a way to have this so the other dimension values would also remain but would just change to 0 or null in the measure?

For example, if you have Category (A,B,C,D) as your columns and you select category B in your slicer then only that one will show and the others will no longer be visible within the pivot. A requirement for an upcoming project is to have A,C, and D still remain but with 0's or null values. Thanks!

[#2]

I know the native response  is that if you have a dimension with four values in a pivot table and a filter is applied to select one of them, the entire pivot will change to only show that value within that dimension.  Is there a way to have this so the other dimension values would also remain but would just change to 0 or null in the measure?

For example, if you have Category (A,B,C,D) as your columns and you select category B in your slicer then only that one will show and the others will no longer be visible within the pivot. A requirement for an upcoming project is to have A,C, and D still remain but with 0's or null values. Thanks!

[PaulDBrown · Accepted solution]

@Anonymous 

One way of doing it:

1) Create a duplicate of your dim table with no relationships with your fact table (in my case I'm duplicating "Channel Table")

2) calculate the measures: (using a sum of forecast in this example)

Sum forecast disconnected calc = IF(MAX('Channel Table'[Channel]) 
                                 = SELECTEDVALUE('Channel Table (2)'[Channel]), 
                                  [Sum Forecast], 0)

and for the totals:

Total Disconnected = 
SUMX(SUMMARIZE('Channel Table', 'Channel Table'[Channel], 
            "Total Forecast", [Sum forecast disconnected calc]), 
                                [Total Forecast])

Use the disconnected Table as your slicer; in the visual, use the field from the DIM table connected to your fact table

and you get this