Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Create a filter with a measure.

Hello all! 

Is there a way to create a filter in the form of a measure?
I want to write like this:

Measure =
CALCULATE(
    expression, 
    [Filter Measure]
)


Is there a similar way to do this?
Otherwise I will have to copy and paste my filter everywhere and it seems like there should be an easier way.

Thank you for taking your time to do this!

Need Help

11 Replies

  • Kumail's avatar
    Kumail
    Icon for Impactful Individual rankImpactful Individual
    5 years ago

    Hello Anonymous 

     

    Yes, definitely.

     

    If you could send sample .pbix that demonstrate what you are looking to get. It would really help providing you a quick solution.

     

    I hope this helps.

     

    Regards
    Kumail Raza

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Kumail,Thank you for helping!

      I have many different fomulas and I want to calculate all of them for YTD, YearBefore and FirstYear like this:
       

      YTD:
CALCULATE(
    expression,
    'Calendar'[Year] = YEAR( SELECTEDVALUE( 'Calendar'[Quarter-Year] ) ),
    'Calendar'[Quarter] <= QUARTER( SELECTEDVALUE( 'Calendar'[Quarter-Year] ) )
)

YearBefore:
CALCULATE(
    expression,
    'Calendar'[Year] = YEAR( SELECTEDVALUE( 'Calendar'[Quarter-Year] ) ) - 1,
)

FirstYear:
CALCULATE(
    expression,
    'Calendar'[Year] = MIN( 'Calendar'[Year])
)


      The user is supposed to be able to choose year and the filters need to change accordingly (at least the first two).
      I hope you understand what I mean 🙂

      • Kumail's avatar
        Kumail
        Icon for Impactful Individual rankImpactful Individual
        5 years ago

        If the user selects the date using the slicer, then you can create a measure that takes in that date and replace that in the dax code that you refered.

         

        I hope this helps (Would love to see Kudo) 

         

        If you want me to write you a solution, just send over a short sample .pbix file.

         

        Regards

        Kumail Raza

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous ,

     

    To my knowledge, you need to create a table with YTD, YearBefore,FirstYear for slicer :

    Then use SWITCH() to calculate different expressions like this:

    Measure = SWITCH(MAX('ForSlicer'[Type]),"YTD",
CALCULATE(
   SUM('Calendar'[Value]),
    FILTER('Calendar','Calendar'[Year] =MAX('Calendar'[Year]) &&
    'Calendar'[Quarter] <= MAX('Calendar'[Quarter]))),

"YearBefore",
CALCULATE(
  SUM('Calendar'[Value]),
   FILTER(ALL('Calendar'), 'Calendar'[Year] =MAX('Calendar'[Year]) - 1)),

"FirstYear",
CALCULATE(
    SUM('Calendar'[Value]),
    FILTER('Calendar','Calendar'[Year] = MIN( 'Calendar'[Year])))
)

    The final output is shown below:

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hello, 
      Thank you for taking your time to respond to this. 
      What I need to do is to have it dynamic so the table would have to change when the user selects different values in a slicer. 
      If the user chooses Q3 2019 then it should show YTD for 2019 compared to entire 2018. 
      If the user chooses Q1 2020 then i should show the first quarter 2020 compared with 2019. 


      Will this work with your solution?