Forum Discussion

Velocity's avatar
Velocity
Icon for Helper III rankHelper III
6 years ago
Solved

Can I use a Measure in Filter?

Hi,

In my data table, i have many records with future dates. I would like to restrict my slicer to show dates up to last week's beginning date. I have created a measure

cutoff date = today() - (weekday(today(),2) - 6)

Now, i want to show only those dates in filter where transaction date is less that cutoff date but it seems you can't use a measure in visual or page or all pages filter. Any suggestions?

 

Thanks.

 

Need Help
  • rajulshah's avatar
    rajulshah
    6 years ago

    Hello Velocitym,

     

    You can use a measure as 'Visual Level Filter'. However you can create a calculated column in the date table that returns zero for the days that you don't want to count in the measure.

     

    On the basis of what I think your requirement is, I have created a calculated column as below:

     

    Last Week StartDay = 
VAR CurrentWeek =  WEEKNUM(TODAY())
VAR LastWeek = CurrentWeek-1
VAR LastWeekBeginningDate = CALCULATE(FIRSTDATE('Date'[Date]),FILTER('Date',WEEKNUM('Date'[Date])=LastWeek))
RETURN IF(WEEKNUM('Date'[Date])>=LastWeek && 'Date'[Date]>LastWeekBeginningDate,1,0)

     

    And you can use this colujmn to filter out the dates, as LastWeekStartDay<>1.

    You can modify the start of the day as you want. I have taken the default start of the week.
    Hope this helps. Please let me know if this doesn't work.

5 Replies

  • rajulshah's avatar
    rajulshah
    Icon for Resident Rockstar rankResident Rockstar
    6 years ago

    Hello Velocitym,

     

    You can use a measure as 'Visual Level Filter'. However you can create a calculated column in the date table that returns zero for the days that you don't want to count in the measure.

     

    On the basis of what I think your requirement is, I have created a calculated column as below:

     

    Last Week StartDay = 
VAR CurrentWeek =  WEEKNUM(TODAY())
VAR LastWeek = CurrentWeek-1
VAR LastWeekBeginningDate = CALCULATE(FIRSTDATE('Date'[Date]),FILTER('Date',WEEKNUM('Date'[Date])=LastWeek))
RETURN IF(WEEKNUM('Date'[Date])>=LastWeek && 'Date'[Date]>LastWeekBeginningDate,1,0)

     

    And you can use this colujmn to filter out the dates, as LastWeekStartDay<>1.

    You can modify the start of the day as you want. I have taken the default start of the week.
    Hope this helps. Please let me know if this doesn't work.

    • Velocity's avatar
      Velocity
      Icon for Helper III rankHelper III
      6 years ago

      Hello rajulshah

       

      I had already worked out Calculated Column solution but was hoping for 'Measure' solution.

       

      Thanks for your input. 

    • Velocity's avatar
      Velocity
      Icon for Helper III rankHelper III
      6 years ago

      amitchandak

       

      I guessed as much. Hence, i had created a calculated column to achieve what i was trying.

       

      Thanks.

      • v-lili6-msft's avatar
        v-lili6-msft
        Icon for Community Support rankCommunity Support
        6 years ago

        hi  Velocity 

        First, you should know that:

        1. Calculation column not support dynamic changed based on filter or slicer.
        2. Measure can be affected by filter/slicer, so you can use it to get dynamic summary result.

        https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/

         

        So you could not use a measure as a slicer.

        "I guessed as much. Hence, i had created a calculated column to achieve what i was trying."  It's pleasant that your problem has been solved, 😁 please accept the reply as solution, that way, other community members will easily find the solution when they get same issue.

         

        Regards,

        Lin