Stop slicer impacting a measure

[Tom_G]

Hi - I'd like to stop a slicer from impacting a measure in a specific case and can't work out how to do this. I've looked at many other posts on this and didn't find a solution yet.

I have a table as below. In reality there are more customer groups and names and other columns.

Customer Group    Customer Name        Amount by Customer Group

A                                        Jack                               8.9

A                                        John                              8.9

B                                        Jim                              528.9
B                                        Jill                               528.9

C                                        Jane                         1818.92

C                                        Justin                        1818.92

I have slicers on Customer Group and Customer Name.

I have some measures built on top of one another to return the amount of 537.8 if Customer Group A AND B are selected in the slicer. They are;

Measure 1 = MAX(Table1[Amount by Customer Group])

Measure 2 = SUMX(DISTINCT(Table1[Customer Group]),[Measure 1])

Measure 3 = CALCULATE([Measure 2],ALLSELECTED(Table1))

The problem is that if I ALSO select John in the Customer Name slicer my measure returns 8.9 cause the measure is being sliced on Customer Name John. How can I get the measure to still return 537.8 if John is selected and Customer Group A and B are selected?

I've tried using the ALL function but to no avail. 

Any help much appreciated!

[V-pazhen-msft]

@Tom_G 
You use ALL() in both measure 2 and measure 3, and not sure if you should use distinct(). Try:

Measure 2 = SUMX(ALL(Table1),[Measure 1])

Measure 3 = CALCULATE([Measure 2],ALL(Table1))

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

[Tom_G]

Hi @V-pazhen-msft thanks for responding.

I tried the above - it seems it makes the measure unresponsive to the Customer Group slicer as well as Customer Name. I was looking that the measure continued changing based on slicing of Customer Group. 

Thanks

[aj1973]

Hi @Tom_G 

You need to go to edit Edit Interaction, select the slicer then go to card and stop the filter.

[Tom_G]

Hi @aj1973, thanks for your answer. If I cancel the interactions between the slicer and visual then the visual does not filter down to John when Customer Name is selected. I need to keep this interaction, it's just the measure itself which should not change. Any way to do this with DAX?

Thanks

[Tom_G]

By following this video, I managed to stop completely the Customer Name slicer affecting the visual. Basically creating a disconnected table and an associated measure.

https://www.youtube.com/watch?v=pEAwvMkpA8I

Technically that gets me 537.8 if John is selected and Customer Group A and B are selected.

However, I still wanted some interaction between Customer Name and the visual. The visual should still filter down to John when it is selected as a Customer Name, it's just that the measure should return 537.8.

Is that possible?