Forum Discussion

isThatAFrog's avatar
isThatAFrog
Frequent Visitor
9 months ago
Solved

DAX filter context - rows not impacted by datesbetween

Hi DAX experts

I have this calculation that I want to work in a table visiual so that I can calculate before and after the slicer applied, but it only works on the total, how to change this?
I have also tried with the Window function but i could not make it work, please help:

CALCULATE(COUNTROWS('Mobility setup')
        ;DATESBETWEEN('Mobility setup'[Effective date]
        ;MINX(ALL('Mobility setup');'Mobility setup'[Effective date])
        ;MAX('Mobility setup'[Effective date])
        )
)

Need Help
Tips and Tricks
  • isThatAFrog's avatar
    isThatAFrog
    9 months ago

    Thank you for the help lbendlin and v-saisrao-msft 

    The sample file was very good to recreate the issue but the measue did little more than countrows()
    You were right to use the date table (I didnt because I had some issues with it) and my initial formula works just fine then 
    With the following formula I got the result, but you could try something similar with EXCEPT() i guess 

        CALCULATE(COUNTROWS('Mobility setup')        
            ;DATESBETWEEN('DateTable'[Date]
                ;MINX(ALL('DateTable');'DateTable'[Date])
                ;MAX('DateTable'[Date])
            )
        )

4 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    9 months ago
    so that I can calculate before and after the slicer applied

    Your slicer needs to feed from a disconnected calendar table.

     

    And then you can use EXCEPT for a more concise filter.

    • isThatAFrog's avatar
      isThatAFrog
      Frequent Visitor
      9 months ago

      Thank you for the help lbendlin and v-saisrao-msft 

      The sample file was very good to recreate the issue but the measue did little more than countrows()
      You were right to use the date table (I didnt because I had some issues with it) and my initial formula works just fine then 
      With the following formula I got the result, but you could try something similar with EXCEPT() i guess 

          CALCULATE(COUNTROWS('Mobility setup')        
              ;DATESBETWEEN('DateTable'[Date]
                  ;MINX(ALL('DateTable');'DateTable'[Date])
                  ;MAX('DateTable'[Date])
              )
          )