Forum Discussion
Count Rows Range of Years
Hello,
I'm trying to use my data to create some different pie charts and bar graphs.
My data set consists of the years 2009 through 2020. The data of interest is strains of a virus, and how many occurances we have of each strain per year.
I've created several measures (Strain A 2010, Strain B 2010, Strain A 2011, Strain B 2011, etc etc) as well as a bunch of countrows DAX that counts Strain A from the entire database, Strain B from the entire database, etc.
Now what I want to do is create a measure that will "countrows" from a range of years (2009-2014, 2015-2020).
Basically looking to create: Measure=Calculate(countrows('table'), 'table'[column]="2009-2014") But obviously this exact thing doesn't work.
*Where [column] is [Year]
Any help is greatly appreciated!
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
15 Replies
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
Good CosmicHorse95,
This is my first post on the forum and I hope I can help you.
You can try to do this with calculated columns as follows:
If you want the ranges of:
2009 to 2014:Rango2009-2014 = IF(YEAR(Calendar[Date]) >= 2009 && YEAR(Calendar[Date]) <= 2014, "True","False")
2015-2020:
Range2015-2020 = IF(YEAR(Calendar[Date]) >= 2015 && YEAR(Calendar[Date]) <= 2020, "True","False")
These calculated columns can be used in the "Count" mode fields and put a filter that only tells you those that are in "True"
In this way you will be able to calculate the total of lines according to the range of year you have indicated.
Greetings,
EstebanHello 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,
Esteban