Creating a new Column that Counts the frequency EACH Value in another column appears

[jamesfry1]

Hi,

I have a table that has the below fields, detailing contacts made to customer  There are roughly 10,000  contacts in the table

Contact reference number (unique)	Customer ID	Date of Contact
1	A123	01/01/2023
2	A123	01/02/2023
3	A123	01/03/2023
4	B456	01/01/2023
5	B456	01/02/2023
6	C789	01/01/2023
7	C789	01/02/2023
8	D123	01/01/2023

I want to add a fourth column that shows for each customer ID how many contacts they’ve had so it would look like below

Contact reference number (unique)	Customer ID	Date of Contact	Contact Count
1	A123	01/01/2023	3
2	A123	01/02/2023	3
3	A123	01/03/2023	3
4	B456	01/01/2023	2
5	B456	01/02/2023	2
6	C789	01/01/2023	2
7	C789	01/02/2023	2
8	D123	01/01/2023	1

The reason for this is that I ultimately want a visual in my report that looks like the below table whereby I can see how many customers have had one contact, how many have had 2 contacts and so on

Number of Contacts	Customers
1	100
2	90
3	80
4	70
5	60
6	50

I’m not sure if it makes a difference but I need the visual table in my report to respond to a date slicer so if the customer has had 6 contacts ever but only 3 contacts in the period selected in my date slicer, that customer would appear on the row with 3 contacts rather than the row with 6 contacts

Thanks for any help on this one!

[v-jianboli-msft · Accepted solution]

Hi @jamesfry1 ,

Please try: First create a new table:

Count Table =
SUMMARIZE (
    ADDCOLUMNS (
        'Table',
        "Count",
            CALCULATE (
                COUNT ( 'Table'[Contact reference number (unique)] ),
                FILTER ( 'Table', [Customer ID] = EARLIER ( 'Table'[Customer ID] ) )
            )
    ),
    [Count]
)

Output:

Then use this measure to create a table visual:

Customers = 
var _a = ADDCOLUMNS('Table',"Count",CALCULATE(COUNT('Table'[Contact reference number (unique)]),FILTER('Table',[Customer ID]=EARLIER('Table'[Customer ID]))))
var _b = SUMMARIZE(FILTER(_a,[Count]=SELECTEDVALUE('Count Table'[ Number of Contacts])),'Table'[Customer ID])
return COUNTROWS(_b)

Final output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DimaMD]

Hi @jamesfry1 try it 

Contact Count = 
CALCULATE( 
    COUNTROWS('table'), 
        FILTER( 
        'table', 
        'table'[Customer ID] = EARLIER([Customer ID]) && 
        [Date of Contact] >= MIN('table'[Date of Contact]) && 
        [Date of Contact] <= MAX('table'[Date of Contact]) 
    ) 
)

[jamesfry1]

Thanks for this, I've tried adding a measure using that code and it generates the response  "EARLIER/EARLIEST refers to an earlier row context which doesn't exist."

[ppm1]

The need to be responsive to slicers makes it a little trickier, but still doable. First you need to make a disconnected table (no relationships) with the potential count values.

ContactCount = SELECTCOLUMNS(GENERATESERIES(1,10,1), "Count", [Value])

And then create a measure like this one, to get your desired visual. Replace T2 with your actual table name.

CustomerContacts =
VAR SelCount =
    SELECTEDVALUE ( ContactCount[Count] )
VAR CountSummary =
    ADDCOLUMNS (
        VALUES ( T2[Customer ID] ),
        "cCount", CALCULATE ( COUNTROWS ( T2 ) )
    )
RETURN
    SUMX ( FILTER ( CountSummary, [cCount] = SelCount ), [cCount] )
​

Pat

[jamesfry1]

Hi Pat, thanks for that response. The disconnected table and the measure seem to have initiallly done the job, however  the customer contacts fields seems to be summing the contacts multiplied by the count. EG the count of 1 is correct, but the count of 2 is twice what it should be and the count of 3 is treble what it should be: 

There doesn't seem to be an option to amend the customer contacts in the value section of my table so I'm a bit stumped here. Thanks again 

[barritown]

Hi @jamesfry1,

Your Contact Count calculated column may look like this:

Contact Count = 
VAR savedID = data[Customer ID]
RETURN CALCULATE ( COUNT ( data[Contact reference number (unique)] ), REMOVEFILTERS ( data ), data[Customer ID] = savedID )