DAX - Removing Slicer Filtering

[Anonymous_226]

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

[Anonymous · Accepted solution]

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.

[lbendlin]

Your slicer needs to be fed from a disconnected table.