cancel
Showing results for
Did you mean:
Highlighted
mnayar Established Member

## Calculated Column - Distinct Count By Another Column and Filter By Column

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

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

1 ACCEPTED SOLUTION

Accepted Solutions Super User

## Re: Calculated Column - Distinct Count By Another Column and Filter By Column

Hello @mnayar  Give this a try

``````Total Business Investment Products =
CALCULATE (
DISTINCTCOUNT ( 'Fact'[AccountNumber] ),
ALLEXCEPT ( 'Fact', 'Fact'[CustomerNumber] ),
'Fact'[Flag] = "Y"
)``````
4 REPLIES 4 Super User

## Re: Calculated Column - Distinct Count By Another Column and Filter By Column

Hello @mnayar  Give this a try

``````Total Business Investment Products =
CALCULATE (
DISTINCTCOUNT ( 'Fact'[AccountNumber] ),
ALLEXCEPT ( 'Fact', 'Fact'[CustomerNumber] ),
'Fact'[Flag] = "Y"
)``````
mnayar Established Member

## Re: Calculated Column - Distinct Count By Another Column and Filter By Column

@jdbuchanan71  This works but when I use it as Filter('Fact','Fact'[Flag])='Y') it doesnt work? Do you know why? Super User

## Re: Calculated Column - Distinct Count By Another Column and Filter By Column

I believe the ALLEXCEPT is removing the fitler added in the FILTER statement.

Icey Super Contributor

## Re: Calculated Column - Distinct Count By Another Column and Filter By Column

Hi @mnayar ,

Is this problem solved?

If it is solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If not, please let me know.

Best Regards
Icey

Announcements   