topic Re: Remove Filter to Calendar in DAX Commands and Tips

Remove Filter to Calendar

joshua1990 — Sun, 07 Aug 2022 13:16:55 GMT

Hi all!

I have a sales table that is linked by [Sales Date] to the calendar table.

Now I have a report with a month slicer to filter a matrix that shows budget vs sales.

This visual shows budget vs sales for each article

But now I would like to show the sales regardles of the slicer.

How can I disable the month filter in the Sales Measure?

Something like CALCULATE ( SUM (Sales[Sales]), ALL(Sales])) is not working.

Re: Remove Filter to Calendar

Jihwan_Kim — Sun, 07 Aug 2022 13:39:19 GMT

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

One of ways to create a model that a slicer does not affect the measure is to create a disconnected table and use the column from the disconnected table as a slicer.

Re: Remove Filter to Calendar

joshua1990 — Sun, 07 Aug 2022 14:04:14 GMT

Jihwan_Kim Thanks, is there any way to solve this without an extra table?

Re: Remove Filter to Calendar

tamerj1 — Sun, 07 Aug 2022 14:39:16 GMT

Hi joshua1990

you can use

CALCULATE ( SUM (Sales[Sales]), CROSSFILTER (Sales[Sales Date], 'Calendar'[Date], NONE ))

Re: Remove Filter to Calendar

Anonymous — Sun, 07 Aug 2022 15:45:55 GMT

Hi joshua1990 you can try CALCULATE (SUM(Sales[sales], ALLEXCEPT(Sales,Sales[article])).

Hope the above works. Please feel free to suggest corrections if any.

Re: Remove Filter to Calendar

foucaultf — Wed, 10 Aug 2022 09:51:26 GMT

Hi joshua1990 ,

Do you want the slicer to filter only on a specific column? According to your description, you want the month slicer to filter only budget, not sales.

Unfortunately, since the slicer is related to table visual, it is not possible to still show all rows when you have selected an item in slicer. Unless you disable the interaction between slicer and visual.

To workaround your requirement, you could create an unrelated table, as mentioned by Jihwan_Kim . You will get the result as shown below. The month slicer only filters budget, not sales.

However, if you want to get the result shown above without creating an unrelated table, there is no better way so far.

Best Regards