Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Filtering Out Values Per Year

Hello,  

 

I'm having issue where my filer is filtering out values for all years and not per year. For example, if I have a list of 10 people with traffic violations between 2010 to 2020. How do I make the graph only show people with more than 2 traffic violations PER YEAR and not all years? 

 

Thank you

Need Help
  • v-yadongf-msft's avatar
    v-yadongf-msft
    3 years ago

    Hi Anonymous ,

     

    This is my test table:

     

    Create a measure:

    Count of violation = 
CALCULATE (
    COUNT ( 'Table'[Name] ),
    FILTER (
        ALLEXCEPT ( 'Table', 'Table'[Year] ),
        'Table'[Name] = SELECTEDVALUE ( 'Table'[Name] )
            && 'Table'[Violation] = "Yes"
    )
)

     

    Create a slicer from [Year] column and create a table visual:

     

    Put the measure into the filter pane:

     

    You can show people with more than 2 traffic violations PER YEAR and not all years.

    Best regards,

    Yadong Fang

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

    Filtering Out Values Per Year.pbix20 KB

3 Replies

  • Thiago_Melo's avatar
    Thiago_Melo
    Frequent Visitor
    3 years ago

    First you have to create an graph (i recommend bar graph) with the years in the X axis (you need an column with all the years on the table), in the Y axis you can use the filter functionImage from microsoft support

     

     

     

     

     

     

     

     

     

     

     

    Select the column (wich i suppose in your example would be the number of traffic violations by person) then filter with "GREATER THAN" and below "2" 

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hello! thank you for commenting on my post. I tried doing what you explained but nothing really fixed issue. The graph is still showing me vlaues with less than 2 because the filter is being applied to all years and not one yer. If I select a single year, the graph still shows me people with less than 2 violations, even though these people didn't have any violations on the selected year.

  • v-yadongf-msft's avatar
    v-yadongf-msft
    Community Support
    3 years ago

    Hi Anonymous ,

     

    This is my test table:

     

    Create a measure:

    Count of violation = 
CALCULATE (
    COUNT ( 'Table'[Name] ),
    FILTER (
        ALLEXCEPT ( 'Table', 'Table'[Year] ),
        'Table'[Name] = SELECTEDVALUE ( 'Table'[Name] )
            && 'Table'[Violation] = "Yes"
    )
)

     

    Create a slicer from [Year] column and create a table visual:

     

    Put the measure into the filter pane:

     

    You can show people with more than 2 traffic violations PER YEAR and not all years.

    Best regards,

    Yadong Fang

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

    Filtering Out Values Per Year.pbix20 KB