Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Cumulative chart filter on Year

Hello,

 

I've been using this DAX code below to produce a cumulative chart.  In the past I've been using different query to separate the years,  now felling more confident I would love too consolidate our reports into a single query using both 2018 - 2019 data together.

 

What is the best way to add filtering to the DAX code below to filter Cumulative value on one single Year?

 

value =

COUNTROWS (

    FILTER (

        ALL ( 'Implemented Changes' ),

        [Week] <= MAX ( 'Implemented Changes'[Week] )

            && [Class] IN FILTERS ( 'Implemented Changes'[Class] )

    )

)

 

Thank you,

Don

Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    6 years ago

    Hi Anonymous ,

     

    ALLEXCEPT should help you here.

     

    value =
COUNTROWS (
    FILTER (
        ALLEXCEPT ( 'Implemented Changes', 'Implemented Changes'[year] ),
        [Week] <= MAX ( 'Implemented Changes'[Week] )
            && [Class] IN FILTERS ( 'Implemented Changes'[Class] )
    )
)

2 Replies

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

    Hi Anonymous ,

     

    ALLEXCEPT should help you here.

     

    value =
COUNTROWS (
    FILTER (
        ALLEXCEPT ( 'Implemented Changes', 'Implemented Changes'[year] ),
        [Week] <= MAX ( 'Implemented Changes'[Week] )
            && [Class] IN FILTERS ( 'Implemented Changes'[Class] )
    )
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hello Frank,

       

      Thank you for your help, your code gets me closer to my objective.

       

      I was looking to isolate each year in a separate Cumulative chart.  I tired to alter your solution (see below) using the "= 2018" but didn't work.

       

       
      Linevalue =
      CALCULATE (
      COUNTA ( 'Implemented Changes'[CLASS] ),
      FILTER ( 'Implemented Changes', 'Implemented Changes'[Year]= 2018 ) ,
      'Implemented Changes','Implemented Changes'[Week] <= MAX ( 'Implemented Changes'[Week] )
      && 'Implemented Changes'[Class] IN FILTERS ( 'Implemented Changes'[Class] )
      )