Calculated Column - Distinct Count By Another Column and Filter

Question

Hello,&nbsp;I have a table with 3 columns, Customer Number, Account Number &amp; 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.&nbsp;CustomerNumber&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; AccountNumber&nbsp; &nbsp; &nbsp; Flag&nbsp; &nbsp; ExpectedOutput&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Actual Output1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;A1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Y&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;31&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;A2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;N&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 32&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;A3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Y&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 32&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;A4&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Y&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 3&nbsp;I am using the following formula&nbsp;&nbsp;Total Business Investment Products =CALCULATE (DISTINCTCOUNTNOBLANK ( 'Fact'[AccountNumber] ),FILTER ( 'Fact', 'Fact'[Flag] = "Y" ),ALLEXCEPT ( 'Fact', 'Fact'[CustomerNumber] ))&nbsp;

ashish_mathur · Accepted Answer

Hi,
This calculated column formula works
=CALCULATE(COUNTROWS(Data),FILTER(Data,Data[CustomerNumber]=EARLIER(Data[CustomerNumber])&amp;&amp;Data[Flag]="Y"))
Hope this helps.

Forum Discussion

Calculated Column - Distinct Count By Another Column and Filter

2 Replies

Related content

DAX Calculation for Distinct Count

calculate sum based on distinct values of another column

Distinct inside a calculate function

Count Distinct Values based on another column in Power Query

count distinct