Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not applicable

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

Hi,

 

I have a below data set with different device IDs

 

Capture.JPG

 

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

4 REPLIES 4
parry2k
Super User
Super User

@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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

Hi,

 

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

 

Current sample data:

idregionReach_numberdeviceiddeduped distinct reach
2366Auckland50D6C8A78-946B-420E-9F70-54D1CF43EFBE5
2366Auckland512A6FECC-2E3B-45F1-A6E1-B88CF6AE8EC65
2366Auckland51CDF407C-B948-4645-858E-4FA11F573AA15
2366Auckland59CAF0DFA-17E4-4A64-BC80-BCAE307A62435
2366Auckland5FDD2FEFE-7EA6-46E0-8948-8B8F016781BB5
2358Auckland40C374096-1BDD-4CD6-827B-F002FA3C6CCB4
2358Auckland47ED6BB7D-9271-433A-9755-FF30A7B2ED354
2358Auckland48D3AC261-725E-4942-BCEF-D747E06462ED4
2358Auckland4F85DDB07-91F3-4619-8304-3BF3518061734
2359Auckland40C374096-1BDD-4CD6-827B-F002FA3C6CCB2
2359Auckland48255BC7B-2C23-4E4C-BF75-3B8729C9F2B72
2359Auckland4CED04E9C-ECF1-4318-82BA-12C504610A582
2359Auckland4F85DDB07-91F3-4619-8304-3BF3518061732
2361Auckland2069D0732-37B3-41BC-849E-B8A7D328EEEE2
2361Auckland2F20042CE-64A4-487D-AA4B-1DDE29C1C2732

 

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

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

Hi,

 

The end result should be "deduped distinct column".

 

Regards,

Waqas

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.