Visualization of duplicated values on a card

[Anonymous]

Hi PBI community:

For a report, we have a data full of bank accounts (i.e. ES45786798466846), some of the bank accounts are duplicated, we would like to visualize the amounf of duplicated bank accounts on a "card visual".

I´ll add here a small example of our data:

ES09324

ES09324

ES09324

ES09324

ES09324

ES72933

ES92348

ES92348

ES92348

ES92348

ES92348

ES93034

ES93034

ES93034

ES93034

ES93034

ES98239

As you can see, we can identify some bank accounts that are duplicated. We are not interested in visualizing the total amount of duplicated values (15, acccording to the image ) but the amount of distinct duplicated bank accounts (3, acccording to the image below). This is explained in the following image.

We would like to visualize, for this data, the value = 3 in a card. This is super easy to see on a matrix visual, because we can use more filters, but on a card visual we are not able, yet, to accomplish that.

Is this possbile? Is there any wayaround? Can we visualize the value of a Matrix on a Card? Like importing it from one visual to another.

Thanks very much. 

[amitchandak · Accepted solution]

@lascurainge , Try these measures

Different account: different(Table[Account])
Duplicate count: sumx(summarize(table,Table[Account], "_1" ,count(Table[Account]),"_1", distinctcount(Table[Account])),[_1] -[_2])
Duplicate account: countx(filter(summarize(table,Table[Account], "_1" ,count(Table[Account]),"_1", distinctcount(Table[Account])),[_1] >[_2]),[Account])

[Anonymous · Accepted solution]

Hello @lascurainge

You can show the discount of duplicate bank accounts on the card by measure.

Measure:

Measure = 
VAR _Count = COUNTAX(FILTER('Table','Table'[BankAccount]=MAX('Table'[BankAccount])),'Table'[BankAccount])
VAR _A = IF(_Count>1,1,0)
RETURN
_A

DisCount Duplicate BankAccount = 
var _T = SUMMARIZE('Table','Table'[BankAccount],"Count",[Measure])
Return
SUMX(_T,[Count])

Or you can create a calculated table and display the bank account count on the visual card.

Calculated table:

Table 2 = 
VAR _T = SUMMARIZE('Table','Table'[BankAccount],"Count",COUNTAX(FILTER('Table','Table'[BankAccount]=EARLIER([BankAccount])),'Table'[BankAccount]))
VAR _T2 = SUMMARIZE(FILTER(_T,[Count]>1),[BankAccount])
Return
_T2

The result is as follows.

You can download the pbix file from this link: Viewing Duplicate Values on a Card

Best regards

Rico Zhou

If this post helps,then consider Accepting it as the solution to help other members find it faster.

[prajaktakeni11]

Hello
 @Anonymous  , 
I tried the same query but its not working in my case .Could you please assist 

[Anonymous]

Thank you very much for the solution, it has worked for me

[Anonymous]

Hi @Anonymous 

I'm glad to be able to solve your problem. You could accept the helpful reply as the solution to help the other members find it more quickly. More people will benefit from it. 

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[amitchandak · Accepted solution]

@lascurainge , Try these measures

Different account: different(Table[Account])
Duplicate count: sumx(summarize(table,Table[Account], "_1" ,count(Table[Account]),"_1", distinctcount(Table[Account])),[_1] -[_2])
Duplicate account: countx(filter(summarize(table,Table[Account], "_1" ,count(Table[Account]),"_1", distinctcount(Table[Account])),[_1] >[_2]),[Account])

[Anonymous]

Thank you very much for the solution, it has worked for me.