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, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Syndicate_Admin
Administrator
Administrator

Working with duplicate values

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 numberType of work orderOrder numberAddress
123Revision445xxxxx
123Maintenance23xxxxx
124Painting45yyyy
124Cleaning78yyyy

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 numberRecount
1232
1242

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.

1 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] ) )
)

View solution in original post

8 REPLIES 8
v-shex-msft
Community Support
Community Support

HI @Anonymous,

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Syndicate_Admin
Administrator
Administrator

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 numberNumber of work orders
client 1232
client 1242

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 NumberOrder TypeOrder numberAddress
123Revision55xxxxxxx
123Painting56xxxxxxx
124Maintenance59yyyyyyyy
124Revision60yyyyyyyy

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.

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.

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.

Captura_tabla resumen.JPG

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

Captura_FILTRO.JPG

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

Captura_actual.JPG

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.

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.

AlexisOlson_0-1633644877814.png

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

 

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
1232
1232
1252
1252

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".

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

 

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

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

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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