Calculated Measure to find duplicates and setting a flag within same column

[Anonymous]

Hi,

I have a below data set with different device IDs

I need a calcuated measure which follows following rules:

Get the highest unduplicated reach (i.e. the highest number of unique device ids) across the network. So for example if you have 3 ids with the following device ids reached:

B1: a, b, c, d

B2: a, b, c

B3: e

Then the calculated measure should be calculating unduplicated reach as:

B1 = 4

B2 = 0

B3 = 1

Hope that makes sense. Please note i need a measure not column coz the values have to change based on different filters.

Regards,

Waqas

[parry2k]

@Anonymous the sample datset you showed, how it is map to b1: a,b,c,d etc. Can you put something in excel sheet and share, sample data and expected result?

Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[Anonymous]

Hi,

That B1 thing is a logic for explanation. Here are the details:

Current sample data:

id	region	Reach_number	deviceid	deduped distinct reach
2366	Auckland	5	0D6C8A78-946B-420E-9F70-54D1CF43EFBE	5
2366	Auckland	5	12A6FECC-2E3B-45F1-A6E1-B88CF6AE8EC6	5
2366	Auckland	5	1CDF407C-B948-4645-858E-4FA11F573AA1	5
2366	Auckland	5	9CAF0DFA-17E4-4A64-BC80-BCAE307A6243	5
2366	Auckland	5	FDD2FEFE-7EA6-46E0-8948-8B8F016781BB	5
2358	Auckland	4	0C374096-1BDD-4CD6-827B-F002FA3C6CCB	4
2358	Auckland	4	7ED6BB7D-9271-433A-9755-FF30A7B2ED35	4
2358	Auckland	4	8D3AC261-725E-4942-BCEF-D747E06462ED	4
2358	Auckland	4	F85DDB07-91F3-4619-8304-3BF351806173	4
2359	Auckland	4	0C374096-1BDD-4CD6-827B-F002FA3C6CCB	2
2359	Auckland	4	8255BC7B-2C23-4E4C-BF75-3B8729C9F2B7	2
2359	Auckland	4	CED04E9C-ECF1-4318-82BA-12C504610A58	2
2359	Auckland	4	F85DDB07-91F3-4619-8304-3BF351806173	2
2361	Auckland	2	069D0732-37B3-41BC-849E-B8A7D328EEEE	2
2361	Auckland	2	F20042CE-64A4-487D-AA4B-1DDE29C1C273	2

So as you can see the first id 2366 has the highest reach of 5 so the device ids for the next id should be deduped based on previous id with highest reach and give deduped distinct reach and so forth.

The best example is id 2359 where the deviceids which are already present in previous ids are excluded to come up with a reach of 2 rather than 4.

Hope that makes sense.

Regards,

Waqas

[parry2k]

@Anonymous sorry man I'm having hard time understanding the requirement. can you put what end result will look like?

[Anonymous]

Hi,

The end result should be "deduped distinct column".

Regards,

Waqas