Forum Discussion
Helper ICounting by Multiple Conditions in Column
Hello,
I am currently trying to make a dynamic column so that when its used it will change according to the Fiscal year. I currently have column that is static. The goal is to count the number of times a certain value appears in the dataset. Rather instead what I would like is to find the number of times the value appears per Fiscal year.
I tried the following with no success. Wondering what other options I have to join these statements to get one output?
Var id_value = 'table 1' [ID]
return
IF('Table 1'[Year] = "2000",
COUNTROWS(FILTER(ALL('Table 1'), 'Table 1'[Year] = "2000" && 'Table 1'[ID] = id_value)))
This returns the initial part I want but I want a calculation for other years as well. I have tried to join similiar statements together but end up with true false rather than an output.
Date looks like this:
ID | Year |
123 | 2019 |
| 345 | 2019 |
| 123 | 2000 |
| 345 | 2000 |
Thank you much appreciated. I do need it in a column as well not a measure as I need to be able to use different aggregates with the column
Thanks
Hello:
You can give this a shot. You almost had it.
Count =var vid = 'Table 1'[ID]var vyr = 'Table 1'[Year]returnCOUNTROWS(FILTER(ALL('Table 1'),'Table 1'[ID] = vid &&'Table 1'[Year] = vyr))I hope this solves the question!
6 Replies
- tackytechtom
Most Valuable Professional
Hi MSW ,
If I understood you correctly, you would like to count the number of IDs grouped by the fiscal year, is this right?
If so, I would have created the measure like this:
Measure = CALCULATE ( COUNT ( 'table 1' [ID] ), ALLEXCEPT ( 'table 1' [Year] ) )Does this help? 🙂
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/ 
MSWtackytechtom This doesn't appear to work. It will not allow for the table 1 Year into the measure. Also ideally would like to make this a column.
- tackytechtom
Hi MSW ,
Please share some data and the result that you would like to achieve 🙂
Thanks!
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/- MSW
ID Year Count 123 2000 2 123 2000 2 345 2000 1 345 2001 1 123 2003 1 Here is what I want returned. Counts the number of times the ID is in the dataset per Year.
Whitewater100Solution Sage
Hello:
You can give this a shot. You almost had it.
Count =var vid = 'Table 1'[ID]var vyr = 'Table 1'[Year]returnCOUNTROWS(FILTER(ALL('Table 1'),'Table 1'[ID] = vid &&'Table 1'[Year] = vyr))I hope this solves the question!
