Multiple Calculations for One Column

[MSW]

Hello,

I am looking for a better solution to find the count of one ID from a column associated to an ID in another column. Ideally would count based on the year. So if a customer with an ID has two order numbers that are the same that would be output as 2. 

Currently working with a formula like this: 

Calculate(Count('Table'[CustomerID]), Filter('Table'[Order Number] = [Customer ID] = Earlier('Table'[CustomerID). 

This is resulting in the wrong output and not based on year.  The goal is to understand how many orders per year a customer has then eventually how many items are in each order with a certain value. 

Any suggestions on how to get this to work more effectively? Thank you

Date	Customer ID	Order Number	Calculated Column Read out
2020	123	100	2
2020	123	100	2
2021	123	101	1
2019	123	102	1
2020	345	200	3
2020	345	200	3
2020	345	200	3
2020	678	300	1
2021	678	302	1
2019	890	400	2
2019	890	400	2
2021	890	402	1

[v-jayw-msft · Accepted solution]

Hi @MSW ,

Do you want count or distinct count?

Is this the expected result?

Column =
COUNTX (
    FILTER (
        'Table',
        'Table'[Date] = EARLIER ( 'Table'[Date] )
            && 'Table'[Customer ID] = EARLIER ( 'Table'[Customer ID] )
    ),
    'Table'[Order Number]
)

Best Regards,

Jay

[MSW]

This works to count the number of Customer IDs in a given year but it does not account for the differnt OrderIDs per customerID per year.

[tamerj1]

@MSW 

If I correctly understand please use

New Column =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Order Number] ),
    ALLEXCEPT ( 'Table', [Customer ID], 'Table'[Date] )
)

[tamerj1]

Hi @MSW 

you can use

New Column =
CALCULATE (
    COUNTROWS ( 'Table' ),
    ALLEXCEPT ( 'Table', [Customer ID], 'Table'[Date] )
)