Calculated column of count occurrences of a string in a table

[setis]

Dear all,

I'm trying to calculate on a calculated column the nr of different names for every CaseID

CaseID Name      CC

  A1       Peter       2

  A2          Peter    1

  A1        Ana         2

  A3        Tom       1

Could somebody point me in the right direction, please?

[v-frfei-msft · Accepted solution]

Hi @setis ,

To create a calculated column as below.

CC = 
CALCULATE (
    DISTINCTCOUNT ( Table1[name] ),
    FILTER ( Table1, Table1[Case ID] = EARLIER ( Table1[Case ID] ) )
)

[Ashish_Mathur · Accepted solution]

Hi,

Try this calculated column

=CALCULATE(DISTINCTCOUNT(Data[Name]),FILTER(Data,Data[CaseID]=EARLIER(Data[CaseID])))

Hope this helps.

[Arjunarao · Accepted solution]

Hi setis,
You can achive with Group by & Merge Transforamtion.
Please check the screens and file.

https://drive.google.com/open?id=1wTm4HXqDz-J_-LugxyQ3RHL-_tkhB4EK

[Ashish_Mathur · Accepted solution]

Hi,

Try this calculated column

=CALCULATE(DISTINCTCOUNT(Data[Name]),FILTER(Data,Data[CaseID]=EARLIER(Data[CaseID])))

Hope this helps.

[v-frfei-msft · Accepted solution]

Hi @setis ,

To create a calculated column as below.

CC = 
CALCULATE (
    DISTINCTCOUNT ( Table1[name] ),
    FILTER ( Table1, Table1[Case ID] = EARLIER ( Table1[Case ID] ) )
)

[Greg_Deckler]

Perhaps something along the lines of:

Column = 
VAR __table = FILTER(ALL('Table6'),[CaseID]=EARLIER([CaseID]))
VAR __people = DISTINCT(SELECTCOLUMNS(__table,"__Name",[Name]))
VAR __distinct = DISTINCT(__people)
RETURN
COUNTX(__distinct,[__Name])