Forum Discussion
Helper IIISelectively filtering visual axis and measures from one slicer
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 > )
Super UserHi 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!
Helper IIIThis 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.