Selectively filtering visual axis and measures from one slicer

[afaro]

I have a column Calendar[Month] which I add to the X axis of a line chart. 

Then, I have two measures let's say - 

1. Cumulative Revenue Last Year (I filter on the year directly using YEAR(TODAY()) in DAX)

2. Cumulative Revenue Present Year (I filter on the year directly using YEAR(TODAY()) in DAX)

which I add in the same line chart visual. 

I add a slicer for Calendar[Month] on the page as well. 

I want this Calendar[Month] slicer to not filter the Calendar[Month] on X axis or the Cumulative Revenue Last Year measure of the visual. However, I still want the Cumulative Revenue Present Year to get filtered by the slicer Calendar[Month]. 

I want to do this by avoiding to create a detached new slicer duplicating Calendar[Month] only for Cumulative Revenue Present Year if possible. 

[MarkLaf · Accepted solution]

---

@afaro wrote:

I want to do this by avoiding to create a detached new slicer duplicating Calendar[Month] only for Cumulative Revenue Present Year if possible. 

---

This is impossible. You need a detached table.

The underlying DAX (can check by copying visual's query in performance analyzer) being run is SUMMARIZECOLUMNS and the slicer selection is added as a filter table that applies to group by columns. There is currently no way to somehow make the TREATAS filter table not filter the group by columns when they are of the same table.

// simplified DAX of visual with Calendar[Month] in x-axis and Calendar[Month] slicer
SUMMARIZECOLUMNS(
	Calendar[Month], // group by column
	TREATAS( { < months selected in slicer > }, Calendar[Month]), // filter table from slicer
	< added name,expression columns >
)

[wardy912]

Can you post your measures please?

[wardy912]

Hi @afaro 

 You need to edit your measure to ignore the slicer by using the ALL() function

Cumulative Revenue Last Year = 
CALCULATE(
    [Cumulative Revenue],
    YEAR(Calendar[Date]) = YEAR(TODAY()) - 1,
    ALL(Calendar[Month])  -- Ignores slicer on Month
)

Leave the other measure as it is to still be affected by the month slicer

I hope this helps, please give a thumbs up and mark as solved if it does, thanks!

[afaro]

This still does filter my X axis and hides the rest of the months from visual so I cannot see them for last year. Show items with no data doesn't work in this case as slicer has already filtered out those months.