topic DAX - Removing Slicer Filtering in DAX Commands and Tips

DAX - Removing Slicer Filtering

Anonymous_226 — Mon, 29 Jan 2024 20:26:16 GMT

Hi All,

I have the following situation I have been trying to figure out with no success:

Some Background:

1. I have a date slicer on a page of my power bi report

2. I have a measure that contains some variables:

- 1 variable gets the MIN date from the slicer as the report user can choose any date from the date table

- My next variable is getting a count of rows that have a date/time that falls in the month chosen in the above date slicer(Ex. December 2023)

- My third variable is getting a count of rows that have a date/time that falls in the month previous to the month chosen above(Ex. November 2023)

- I then have a switch in that same measure that outputs some next based on whether November is more or less than December's number.

My issue:

The date slicer is filtering the data before we get to my third variable so I always get no results for it....I cant turn off the date slicer interaction though because I am using a between date slicer and if i turn it off, then I have no way to return the MIN value from the slicer.

I have tried ALL, ALLSELECTED and REMOVEFILTERS fuction to remove the filtering from the date slicer but nothing seems to work.

Any help is greatly appreciated

Re: DAX - Removing Slicer Filtering

lbendlin — Tue, 30 Jan 2024 00:51:22 GMT

Your slicer needs to be fed from a disconnected table.

Re: DAX - Removing Slicer Filtering

Anonymous — Wed, 31 Jan 2024 06:07:27 GMT

Hi Anonymous_226

lbendlin Good share!

For your question, here is the method I provided. Please try the following steps:

Here's some dummy data

"Date"

Date = CALENDAR("09/01/2023", "02/25/2024")

"Table"

Create a slicer.

Create measures.

1_min_date = MIN('Date'[Date])

2_month_count = CALCULATE( COUNTROWS('Table'), FILTER( ALL('Table'), MONTH('Table'[Date]) = MONTH('Table'[1_min_date]) && YEAR('Table'[Date]) = YEAR('Table'[1_min_date]) ) )

3_last_month_count = var _year = IF( MONTH('Table'[1_min_date]) = 1, YEAR('Table'[1_min_date]) -1 , YEAR('Table'[1_min_date]) ) RETURN CALCULATE( COUNTROWS('Table'), FILTER( ALL('Table'), MONTH('Table'[Date]) = MONTH(EDATE('Table'[1_min_date], -1)) && YEAR('Table'[Date]) = _year ) )

4_max_count = IF( 'Table'[2_month_count] <= 'Table'[3_last_month_count], 'Table'[3_last_month_count], 'Table'[2_month_count] )

If you're still having problems, provide some dummy data and the desired outcome. It is best presented in the form of a table.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.