Counting by Multiple Conditions in Column

[MSW]

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

[Whitewater100 · Accepted solution]

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]

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

[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.

[tackytechtom]

Hi @MSW ,

Here a calculated column:

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

Hope this helps 🙂

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

[tackytechtom]

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/