Forum Discussion
Count Rows Range of Years
Hello again
Sorry if you didn't explain me better,
IF(YEAR(Calendar[Date]) >= 2015 && YEAR(Calendar[Date]) <= 2020, "True","False")
"Calendar" you must change it to the name of the database, "Date" should change it to the column where you have specified the year.
As I have seen in the capture of the other message that you have answered you have the column called "Year" and you only have the year indicated, so you should change the calculated columns that I told you before as follows:2009 a 2014:
Rango2009-2014 = IF("Nombre_Base_Datos"[Año] >= 2009 && "Nombre_Base_Datos"[Año] <= 2014, "True","False")2015-2020:
Range2015-2020 = IF"Nombre_Base_Datos"[Year] >= 2015 && "Nombre_Base_Datos"[Year] <= 2020, "True","False")
I will attach a couple of screenshots indicating how to create the calculated columns to make everything clearer.
I've replicated the example you've posted to try to help you as much as possible.
I hope it has become clearer and if it has helped you do not forget to score!
Greetings,
Estebanthere a variety of ways you can do this. Pie charts are not that easy to do comparisons, either clustered or stacked bar charts are easier. If you decide to use the 2 pie charts, you need to filter the group-range calculated column in the filter pane to select the group you want in that chart.
filter pane
i re attach the file with the visuals in them, and yes this is the same calculated column i created with the count measure
can you provide a sample of your data? Its hard to know what to advise without knowing what it looks like, also provide it in text form.
What are you trying to achieve, what is the goal? insight?
I am not trying to be rude, but I answered all your questions in my own question.
I apologize, I can see some areas where I wasn't super clear. Forgive me.
I'm trying to make a DAX that will count the number of rows through multiple years. I want it to count all the rows that include the years 2009, 2010, 2011, 2012, 2013, and 2014 in the [Year] column.
The goal is to create visuals with this information. Using only the data in the selected range, and counting/evaluating the strains with the selected data.
It is difficult to provide sample data when the data is confidential, but i did my best.
| Year | Sample Name | Age | Strain | Strain 2 | Final Call | Strain D Variety? | Hybrid? | Classic? |
| 2009 | Sample #1 | 21 | Strain A | Strain A | No | No | No | |
| 2009 | Sample #2 | 21 | Strain A | Strain A | Yes | No | No | |
| 2009 | Sample #3 | 21 | Strain A | Strain A | Yes | Yes | No | |
| 2009 | Sample #4 | 21 | Strain C | Strain C | Yes | Yes | No | |
| 2009 | Sample #5 | 21 | Strain E | Strain E | Yes | Yes | No | |
| 2010 | Sample #6 | 21 | Strain B | Strain B | Yes | Yes | No | |
| 2010 | Sample #7 | 21 | Strain B | Strain B | No | No | No | |
| 2010 | Sample #8 | 21 | Strain D | Strain D | No | No | No | |
| 2010 | Sample #9 | 21 | Strain D | Strain D | No | No | No | |
| 2011 | Sample #10 | 21 | Strain E | Strain E | No | No | No | |
| 2011 | Sample #11 | 21 | Strain F | Strain F | No | No | No | |
| 2011 | Sample #12 | 21 | Strain F | Strain F | No | No | No | |
| 2011 | Sample #13 | 21 | Strain F | Strain F | No | No | No | |
| 2011 | Sample #14 | 21 | Strain A | Strain A | No | No | No | |
| 2011 | Sample #15 | 21 | Strain C | Strain C | No | No | No | |
| 2011 | Sample #16 | 21 | Strain A | Strain A | No | No | No | |
| 2013 | Sample #17 | 21 | Strain A | Strain A | No | No | No | |
| 2013 | Sample #18 | 21 | Strain A | Strain A | No | No | No | |
| 2013 | Sample #19 | 21 | Strain F | Strain F | No | No | No | |
| 2014 | Sample #20 | 21 | Strain E | Strain E | No | No | Yes | |
| 2014 | Sample #21 | 21 | Strain E | Strain E | No | No | No | |
| 2014 | Sample #22 | 21 | Strain B | Strain B | No | No | No | |
| 2014 | Sample #23 | 21 | Strain B | Strain B | No | Yes | No | |
| 2015 | Sample #24 | 21 | Strain B | Strain B | No | No | No | |
| 2015 | Sample #25 | 21 | Strain A | Strain A | No | No | No | |
| 2015 | Sample #26 | 21 | Strain A | Strain A | No | No | No | |
| 2015 | Sample #27 | 21 | Strain A | Strain A | Yes | No | No | |
| 2016 | Sample #28 | 21 | Strain A | Strain A | No | No | No | |
| 2016 | Sample #29 | 21 | Strain A | Strain A | No | No | No | |
| 2017 | Sample #30 | 21 | Strain F | Strain F | Yes | No | No | |
| 2017 | Sample #31 | 21 | Strain F | Strain F | No | No | No | |
| 2018 | Sample #32 | 21 | Strain B | Strain B | No | No | No | |
| 2019 | Sample #33 | 21 | Strain B | Strain B | Yes | Yes | No | |
| 2019 | Sample #34 | 21 | Strain E | Strain E | No | No | No | |
| 2020 | Sample #35 | 21 | Strain E | Strain E | No | No | No | |
| 2020 | Sample #36 | 21 | Strain C | Strain C | No | No | No |
well not trying to be rude back, but you did not provide the data. No one can really give you accurate advice without knowing what your data looks like.
If you read the advisory post at the top of this forum it says that if you want to get your questions quickly provide as much info as possible, and you should provide sample data.
Is this what you are looking for? You can create a calculated column on your table with a switch statement. Similar to what is above. see pbix attached
Oops, I apologize, I just saw this response.
I just amended my first reply to you trying to offer a little more detail. You're right, in hindsight I didn't provide as much detail as I thought I was.
I really appreciate your time and help
This is almost exactly what I'm looking for, but my goal is to have two separate pie charts. Can the measurement be split to have the two individual ranges?
what is it that you are wanting to show on a pie chart?
I agree, I like the column/bar charts myself. Unfortunately, in reality there are more than 7 strains and the bar graphs get extremely cluttered like that, and my supervisor doesn't like them as much as the pie charts.**I'm dumb, I was looking at the image all wrong. The clustered column that you have might actually be better than the pie charts! Is that utilizing the same measurement that you originally shared with me?
Ideally, two different pie charts.
Pie Chart 1: Shows the count of each strain between years 2009 and 2014
Pie Chart 2: Shows the count of each strain between years 2015 and 2020
Does that makes sense? Maybe I'm not explaining it very well. I'm sorry I can't show you the complete data to fill in the gaps.