Count the value change in a column

[Violent_Democracy]

Hi,

I have the following dataset:

FacilityRefNo	TenancyRefNo	TenantName	UnitNo	StreetNo	StreetName	Suburb	Postcode
100001	4823	MR DAVID SMITH	1	1	FIRST ST	BLUEBIRD	2000
100001	5001	MISS KELLY JOHNSON	1	1	FIRST ST	BLUEBIRD	2000
100001	5001	MISS KELLY JOHNSON	1	1	FIRST ST	BLUEBIRD	2000
100003	3006	MISS LESLEY HUGHES		13	KINGFISHER ST	STARLING	2001
100003	3006	MISS LESLEY HUGHES		13	KINGFISHER ST	STARLING	2001
100003	3006	MISS LESLEY HUGHES		13	KINGFISHER ST	STARLING	2001
100004	0606	MR TONY ADAMS		12	WATSON ST	GOTHAM	2002
100004	8787	MR SIDNEY CROSBY		12	WATSON ST	GOTHAM	2002
100004	9999	MR WAYNE GRETSKY		12	WATSON ST	GOTHAM	2002

I would like to add a column to the table that shows the number of TenancyRefNo changes that occur in FacilityRefNo.

FacilityRefNo	TenancyRefNo	Changes	TenantName	UnitNo	StreetNo	StreetName	Suburb	Postcode
100001	4823	1	MR DAVID SMITH	1	1	FIRST ST	BLUEBIRD	2000
100001	5001	1	MISS KELLY JOHNSON	1	1	FIRST ST	BLUEBIRD	2000
100001	5001	1	MISS KELLY JOHNSON	1	1	FIRST ST	BLUEBIRD	2000
100003	3006	0	MISS LESLEY HUGHES		13	KINGFISHER ST	STARLING	2001
100003	3006	0	MISS LESLEY HUGHES		13	KINGFISHER ST	STARLING	2001
100003	3006	0	MISS LESLEY HUGHES		13	KINGFISHER ST	STARLING	2001
100004	`0606	3	MR TONY ADAMS		12	WATSON ST	GOTHAM	2002
100004	8787	3	MR SIDNEY CROSBY		12	WATSON ST	GOTHAM	2002
100004	9999	3	MR WAYNE GRETSKY		12	WATSON ST	GOTHAM	2002

I have tried to add a new measure called Tenancy change and use the expression

Sum(Aggr(count(DISTINCT TenancyRefNo),FacilityRefNo))

but it didn't quite give me the output I was expecting at times, also I need to be able to filter on the column as well.

Is anyone able to assist me further here?

Thanks

[Vegar · Accepted solution]

Try this

count(TOTAL <FacilityRefNo> DISTINCT TenancyRefNo)

[Violent_Democracy]

Thanks for that, worked brilliantly.

[Vegar]

Glad to be of help.

Have a good day.

/Vegar

[basav]

Hi, 

You have selected the wrong post as an answer, please select the post of Mr.Vegar with the solution. Thank you. 

cheers,

Basav