Count distinct duplicate entries across entity

[lherbert501]

Hi,

I need to count duplicate instances across email addresses and entity. E.g. if joebloggs@test.com is there 3 times across one of the two entities then this is 1. If it's got 1 entry on each entity this is fine.

I have a calculated column which calculates it correctly, but as soon as I go to create some form of measure it just times out.

I know its not the data volume as everything else is fine. 

Calculated column :

EmailCountPerEntity =

CALCULATE(

    COUNTROWS('Main Data'),

    ALLEXCEPT('Main Data', 'Main Data'[EmailAddress1], 'Main Data'[Entity])

)

Is there a better way to do this, or maybe a measure on its own to avoid the calculated column as it's just going onto a tablix?

Thanks

Liam

[v-sgandrathi]

Hi @lherbert501,

Thank you @MohamedFowzan1, @Ritaf1983, @speedrampsfor your response regarding the issue.

We’re pleased to know that your query has been resolved. If the solution provided by the community member resolved your concern, please confirm.

Should you have any additional questions or require further assistance, feel free to reach out.

Thank you for your time.

[MohamedFowzan1 · Accepted solution]

Hi @lherbert501 

Could you confirm if this is what you are looking for:
DistinctEmailEntityCount =
COUNTROWS(
SUMMARIZE(
TrialEntity,
TrialEntity[Email],
TrialEntity[Entity]
)
)

I tried this out in sample data:
Actual table I created:

Visual and measure added to table:

If you are just trying to count the duplicate:

DuplicateEmailEntityCount =

COUNTROWS(

    FILTER(

        SUMMARIZE(

            TrialEntity,

            TrialEntity[Email],

            TrialEntity[Enity],

            "CountRows", COUNTROWS(TrialEntity)

        ),

        [CountRows] > 1

    )

)

[lherbert501]

Thankyou @MohamedFowzan1 and @Ritaf1983 

[Ritaf1983]

Hi @lherbert501 

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[speedramps]

I want to help you but your description is too vague. Please write it again.

You will get a quicker and better response without misunderstandings if you put time and effort into carefully writing a clear problem description with example input and output data. Look forward to helping you when this information is forthcoming

* Please DON'T copy & paste your DAX that does not work and expect us to fathom what you want. (That is just crazy). ‌‌
* Please DO give a simple non-technical functional description of what you want
* Keep it simple and break large projects into smaller questions and ask just one question per ticket.
* Rename columns to user friendly names. Avoid your own system jargon that we may not understand.
* Most importantly please provide example input data as table text (not a screen print) so helpers can import the data to build a solution for you. (Learn how to share data below)
* Provide the example desired output, with a clear step-by-step description of calculations and the process flow. Clearly explaining the acceptance criteria.
* Take time and care to use the same table and field names in the input, output and description so we can understand your problem and help you.
* Remove any unneeded tables, rows or columns which may cause confusion. Keep it short and concise with the minimal information regarding the key problem.
* Remember not to share private data ... we don't want you to get into trouble. ‌‌
* Please click the thumbs up button for these helpful hints and tips. Thank you.

Learn how to attach data in the forum using OneDrive:-
* Save your file in a OneDrive folder
* Right click on the file and click the “Share” blue cloud icon
* Click the bottom “Copy” button
* Click” Anyone with link can edit”
* Click “Can Edit”
* Click “Can View”
* Click “Apply” button
* Click “Copy”
* Paste the generated link via the forum, email, chat, or any other method.
* Helpers can then download your data, build a solution and share it back.

Learn how to attach data in the forum using Dropbox:-
1. Open Dropbox: Access the Dropbox folder on your computer or through the Dropbox web interface.
2. Select File/Folder: Find the file or folder you want to share.
3. Click Share (or Get Link): Look for a "Share" option or a similar "Get Link" option.
4. Choose Permissions: Decide whether to allow "view only" or "view and download" access.
5. Copy and Share: Copy the generated link and share it with anyone via the forum, email, chat, or any other method.