Pivot chart for control of duplicates

[educastri83]

Hello everyone,

community I come to you to solve this:
I would like to create a pivot chart that shows the following dimensions:
- "Identity Number" that have more than one account (this happens through duplicate users with different account names)
- "Role" that appear more than once in the same "Identity Number"

As expression:
- To be able to count the amount of the total of roles repeated by "Identity Number"

In the example I leave a sample of how the desired pivot chart should be.

Although it is not a priority for now, there are some dirty values in the "Identity number" field, for example: the user with the identity number 33333333 is the same as the 3333333-3 and I would like to clean and validate that value to have accurate roles repeated by user.

Thank you!

[PrashantSangle]

1: take
Dimension : Identity number, Account, Role
Measure : Count(Total<Account> Distinct Role)

2: use Replace function to remove "-"
Replace([Identity Number],'-','')

Regards,
Prashant Sangle

[ramachandra_gee]

Hi Look at the attached one and let me know if any queries.

Thanks,'

'Ram.

[ramachandra_gee]

Hi,

Expected output as in attachment please check and let me know.

Thanks,

Ram

[educastri83]

Hi! thank you for your answer!

almost almost solved… The list of the pivot chart should not include cases that are not repeated more than once by identity number.

For example for the following cases it does not have a role that it appears more than once by Identity Number and they are some of those that should not be shown on the table:

[educastri83]

Thanks Prashant!
The function replace it works

[PrashantSangle · Accepted solution]

use if condition.
try below
if(Count(Total<Account> Distinct Role)>1,Count(Total<Account> Distinct Role))

Regards,
Prashant Sangle

[educastri83]

very nearly @PrashantSangle ,

The result you are giving is if the account appears> 1 but the calculation that should be shown when a role appears> 1 in the same Identity number.

for example, in the case of identity number 12345678 it has that role> 1

Attach the qvw. file example of the value you gave me and a text object with some comments.

Thank you