Forum Discussion

Anonymous_226's avatar
Anonymous_226
Frequent Visitor
2 years ago
Solved

DAX - Removing Slicer Filtering

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

"DAX"
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

     

     

     

     

     

     

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.