Count Rows Range of Years

[CosmicHorse95]

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!

[Syndicate_Admin · Accepted solution]

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,
Esteban

[Syndicate_Admin]

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,
Esteban

[CosmicHorse95]

Hi Esteban, 

Thank you for your help, though i'm afraid I'm not sure how to use the formulas you provided. What is calendar? How do i create a calculated column?

I'm so sorry for the trouble! Thank you

[Syndicate_Admin · Accepted solution]

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,
Esteban

[CosmicHorse95]

Thanks Esteban! The screenshots were super helpful!

Do you know if there's any way to use that column then to make a pie chart?

The goal is to make a pie chart that consists only of the data that falls between those ranges (2009-2014 and 2015-2020), but that includes the strains. As of now, when I try to make a pie chart with 2009-2014 column, and "strain" as the legend, I still get all the data from the entire spreadsheet.

I don't know if I explained that clearly, I'm sorry in advance

[vanessafvg]

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?

[CosmicHorse95]

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

[vanessafvg]

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

[vanessafvg]

wouldn't it be easier to read as a bar chart?  

[vanessafvg]

?

[vanessafvg]

what is it that you are wanting to show on a pie chart?

[CosmicHorse95]

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.

[vanessafvg · Accepted solution]

there 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

[CosmicHorse95]

Thank you so so much for walking me through this!!! This is perfect!

[CosmicHorse95]

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?

[CosmicHorse95]

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