cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
mnayar Established Member
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

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

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
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"
)

View solution in original post

4 REPLIES 4
Super User
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"
)

View solution in original post

mnayar Established Member
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
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
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

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (3,532)