Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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...
Need Help
  • jdbuchanan71's avatar
    jdbuchanan71
    6 years ago

    Hello Anonymous  Give this a try

    Total Business Investment Products =
CALCULATE (
    DISTINCTCOUNT ( 'Fact'[AccountNumber] ),
    ALLEXCEPT ( 'Fact', 'Fact'[CustomerNumber] ),
    'Fact'[Flag] = "Y"
)
jdbuchanan71's avatar
jdbuchanan71
Super User
6 years ago

Hello Anonymous  Give this a try

Total Business Investment Products =
CALCULATE (
    DISTINCTCOUNT ( 'Fact'[AccountNumber] ),
    ALLEXCEPT ( 'Fact', 'Fact'[CustomerNumber] ),
    'Fact'[Flag] = "Y"
)
Anonymous's avatar
Anonymous
Not applicable
6 years ago

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

 

 

  • jdbuchanan71's avatar
    jdbuchanan71
    Super User
    6 years ago

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