Working with duplicate values

[Syndicate_Admin]

Good afternoon friends, I'm pretty new working with power bi. I'm doing a report where I need to work with duplicate values. In fact, the purpose of this report is to know those duplicate values for debugging.

Customer number	Type of work order	Order number	Address
123	Revision	445	xxxxx
123	Maintenance	23	xxxxx
124	Painting	45	yyyy
124	Cleaning	78	yyyy

What the company needs to know is: How many work orders does each client have? The company is not interested in customers with an order, it is interested in customers with more than one. I make a summary and it comes back to me that there are customers with more than one order:

Customer number	Recount
123	2
124	2

So far so good, but they want the duplicates to be extracted from table number 1, and not to show any value other than those duplicates. I try to do it with filters but I can't filter anything else that I occupy. Of your help, please.

[v-shex-msft]

HI @FRANKRE,

Did these suggestions help with your scenario? if that is the case, you can consider Kudo or accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[Syndicate_Admin]

Thank you for responding, and I'm quite novice and ignorant at this my brother, but what I want is this:

In the tab I will show 2 tables, a summary table (with this I have no problem, I have managed to generate it well)

And a second table, where the information of the duplicate values is detailed. What I want is this:

Summary table:

Customer number	Number of work orders
client 123	2
client 124	2

This would be the summary table, and with which I have no problems. I have the problem in the detail table, which I want to show as follows:

Customer Number	Order Type	Order number	Address
123	Revision	55	xxxxxxx
123	Painting	56	xxxxxxx
124	Maintenance	59	yyyyyyyy
124	Revision	60	yyyyyyyy

If you notice, the customer number can be repeated. Client 123 and 124 each have 2 work orders, but there is a difference in the field: order type. What the company wants to know is, which customers have more than one work order. I have another table where I show all the clients and their orders, but this tab wants to focus on knowing which clients have been generated more than one Work Order to be able to attend in the same visit or in any case, be eliminated and leave each client with an order. What I can't apply is the filter to avoid displaying repeated information. I've tried filters and can't do anything. I also tried to create a column and use "count", but it returns the total or "distinticount", but it didn't work for me either. What I want, if possible, to create a support table with the code of each client and that counts how many orders each one has to then show those that have more than one order. I hope I have made myself understood.

[AlexisOlson]

You restated your problem but didn't really answer either of my questions.

It sounds like you are not working in the Power Query Editor but I'm still not sure what your final result should look like. Are you trying to create a new visual or filter an existing one (or something else like creating a calculated table)? If you want a new visual, what should it look like? Your summary table already shows what customers have duplicates, so I'm assuming you want it to show something other than just customer.

[Syndicate_Admin]

Thank you for your help and for your time my friend. I will try to explain better and place images of my gdp in one go.

This is my summary table. Through the report it was discovered that there were customers (customer numbers) who had more than one order generated. So far so good.

If I select the customer number or quantity it shows me (in another table) more details of that client. In this way:

All correct so far, the problem arises when in the table on the left (summary table) there is no filter:

The detail table (last photo) shows me all the clients (customer number) without exception, and I do not want to see all the clients, I just want to see the clients (customer number) who have more than one work order (NUM_OS). I want in some way, to be able to apply some filter where in the detail table only shows the clients who have more than one work order. I don't know if it is necessary to create another table, or some query, or add more fields that help me achieve it. Just like the summary table, which only shows the clients I need (it has filter applied to show those who have more than one work order), do it also in the detail table, which without applying any filter, only shows me information that I need.

[AlexisOlson]

Can you apply the same filter to the big table as to the summary table?

You might need to define a measure and drag that into the filters pane for that visual.

Cantidad = COUNTROWS ( ALLEXCEPT ( Table1, Table1[ClientID] ) )

[Syndicate_Admin]

For a strange reason, by doing that, putting the filter, or doing it with a dax measurement, it does not filter what I want to see, but it distorts the information. I was thinking of making a calculated field from the table where I capture the information, but I can't do it either. Here's an example of what I'm trying to do:

NISRAD	Calculated field
123	2
123	2
125	2
125	2

In my data table the customer number appears as NISRAD, and on the right, calculated field, is what I intend to do. I want the calculated field column to count how many times the customer number was found. For example, 123 is 2 times, I want you to place me 2. I tried the count function, but it doesn't work for me for this. How can I do? My table is called "OS Pending" and the field I want to count "NISRAD".

[AlexisOlson · Accepted solution]

Yeah, you could try a calculated column instead of a measure.

Cantidad =
COUNTROWS (
    FILTER ( 'OS Pending', 'OS Pending'[NISRAD] = EARLIER ( 'OS Pending'[NISRAD] ) )
)

[AlexisOlson]

Are you working in the query editor or with DAX? What is the end result you want?