Calculated Column - Distinct Count By Another Column and Filter

[ThisIsMe]

Hello,

I have a table with 3 columns, Customer Number, Account Number & Flag. I want to create a calculated column(NOT A MEASURE) which counts the number of accounts per customer when the flag is set to yes.

CustomerNumber            AccountNumber      Flag    ExpectedOutput           Actual Output

1             A1         Y            1               3

1             A2         N           1                3

2             A3         Y            2                3

2             A4         Y            2                3

I am using the following formula 

Total Business Investment Products =
CALCULATE (
DISTINCTCOUNTNOBLANK ( 'Fact'[AccountNumber] ),
FILTER ( 'Fact', 'Fact'[Flag] = "Y" ),
ALLEXCEPT ( 'Fact', 'Fact'[CustomerNumber] )
)

[Ashish_Mathur · Accepted solution]

Hi,

This calculated column formula works

=CALCULATE(COUNTROWS(Data),FILTER(Data,Data[CustomerNumber]=EARLIER(Data[CustomerNumber])&&Data[Flag]="Y"))

Hope this helps.

[Anonymous]

Column = CALCULATE(COUNT(Data[AccountNumber]),ALLEXCEPT(Data,Data[CustomerNumber]),Data[Flag]="Y")

@ThisIsMe