Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
MSW
Helper I
Helper I

Counting 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
3452019
1232000
3452000

 

 

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

1 ACCEPTED SOLUTION
Whitewater100
Solution Sage
Solution Sage

Hello:

You can give this a shot. You almost had it.

 

Count =
var vid = 'Table 1'[ID]
var vyr = 'Table 1'[Year]
return
COUNTROWS(
FILTER(ALL('Table 1'),
'Table 1'[ID] = vid &&
'Table 1'[Year] = vyr)
)
I hope this solves the question!

View solution in original post

6 REPLIES 6
Whitewater100
Solution Sage
Solution Sage

Hello:

You can give this a shot. You almost had it.

 

Count =
var vid = 'Table 1'[ID]
var vyr = 'Table 1'[Year]
return
COUNTROWS(
FILTER(ALL('Table 1'),
'Table 1'[ID] = vid &&
'Table 1'[Year] = vyr)
)
I hope this solves the question!
MSW
Helper I
Helper I

@tackytechtom 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.

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/



Did I answer your question➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn

#proudtobeasuperuser 

IDYearCount
12320002
12320002
34520001
34520011
12320031

 

Here is what I want returned. Counts the number of times the ID is in the dataset per Year.

Hi @MSW ,

 

Here a calculated column:

tomfox_0-1649192200229.png

 

Column =
CALCULATE (
COUNT ( 'table'[ID] ),
ALLEXCEPT ( 'table', 'table'[ID], 'Table'[Year])
)

 

Hope this helps 🙂

 

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/



Did I answer your question➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn

#proudtobeasuperuser 

tackytechtom
Super User
Super User

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/



Did I answer your question➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn

#proudtobeasuperuser 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.