How to include specific table dimension value using calculate formula (ignore slicer)?

[ap]

Hi,

I was wondering if this is possible. 

I have a table that shows Product Group, Sales and Closed Year.

I have a Closed Date Slicer to the report. 

I would like the Closed Date Slicer to filter the Closed Year BUT ignore the filter if Product Group is C.

Essentially, I always want Product Group C to always show up even if I apply a slicer year filter that is not in 2016.

Is that possible?

Thanks,

[v-jiascu-msft · Accepted solution]

Hi @ap,

Please check out the demo in the attachment. No relationship can be established.

Measure =
IF (
    MIN ( 'Table1'[Product Group] ) = "C",
    9999,
    IF (
        MIN ( Table1[Closed Year] ) = SELECTEDVALUE ( Table2[Closed Date Slicer] ),
        2,
        BLANK ()
    )
)

Best Regards,

Dale

[Seward12533]

If you don't need to aggregate totals and your only showing rows with the filter conext of a single product group this shoudl work

Write another meaure that is only used to disply in the visual

Sales Display = IF(SELECTEDVALUE(Product Group)="C",CALCULATE([Sales],ALL(table[Closed Year])),[Sales]).  

If closed year is column you may need to do something simalar for "Closed Year"

Closed Year Display = SELECTEDVALUE(table[Closed Year])

If you need to have aggregated totals (i.e. you add something before product group like geography and you want to show the breakout by Geogragy and then Product then this won't work and the aggregated totals wont' indluce the broader filter contect for C.  You woudl need to write a few measures for "All Except C" and "C only" using SUMX with filter conidtions.