Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
gmasta1129
Helper III
Helper III

Remove Duplicate Values in Column

Hello,

 

I am trying to create a table by Client ID using the data in the top table of the screenshot below.  

 

When summing the eligible amount by client id, I receive a value of $10,000,000 for client id #1 in my table.  This value should be $5,000,000 since the master port code is the same (1111).  Its the same portfolio. This value is being doubled when summing by client id.  

 

We have it broken down into two lines because the portfolio code is different and some of the other columns (not shown here) in the report do contain different numbers so i cannot remove the entire row as i need those values for other reports i am creating but the eligible amount will always be the same value as long as the master port code is the same #.   

 

How can i create this table so only 5,000,000 will pull in instead of 10,000,000? 

 

gmasta1129_3-1643835104464.png

 

 

 
 

 

 

 

 

 

 

 

1 ACCEPTED SOLUTION

@gmasta1129, two ways to achieve what you're after is:

 

1. Create a unique key that merges Client ID and Post Code, OR

2. Use MAX to return the maximum amount for the respective Client ID.

 

Firstly, though, I recommend you attempt the earlier options I provided so that assumptions are tested for fact and proven not to work.

 

All the best with your challenge.

 

Theo 🙂

 

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

View solution in original post

8 REPLIES 8
gmasta1129
Helper III
Helper III

I created a new table using the same exact csv file and then created a key as you mentioned above and it worked.  Thanks so much for all your help! 🙂

Great to hear it worked @gmasta1129 ! Well done!

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

TheoC
Super User
Super User

Hi @gmasta1129 

 

Okay, so I've put the following Calculated Column together for you and provided you with the output and how it achieves what you are after:

 

 

Return Max Value = 

VAR _1 = CALCULATE ( COUNTROWS ('Table' ) , FILTER ('Table' , 'Table'[Client ID] = EARLIER ('Table'[Client ID] ) ) )

RETURN

IF ( _1 = 1 , 'Table'[Amount] , MAX ('Table'[Amount] ) )

 

 

The AMOUNT column is all amounts to each Client ID added.  I've purposely made Client ID "2" have two records of the same value (i.e. 32 and 32 which total equals 64, similar to your $5m and $5m).

 

TheoC_1-1643869224982.png

 

All the best and I hope this achieves the solution you're after 🙂

Theo

 

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

gmasta1129
Helper III
Helper III

Hello,

 

@TheoC 

I am not able to remove duplicates because my data has other columns that are needed for tables I created.  

 

 

Hi @gmasta1129 

 

Okay, no worries. The reason the $10,000,000 is showing is because you have two distinct Post Codes for Client 1.  Client 1 has Post Code 1111 and Post Code 1112 and each of these have $5,000,000 (therefore, $5m x 2 = $10m).   You can do either of the following:

 

  1. Create a Key using calculated column ( Column = Table[Client] &" " & Table[Post Code] ) OR
  2. You can remove the Post Code column from your table OR
  3. Correct the Post Code on Client 1 so that Client 1 only has one Post Code.

Hope this helps 🙂

Theo

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

@TheoC  I dont think those will work since i have other columns i am pulling into the table.  The example above was just a short version but i have about 10 more columns where the other columns need to pull in data for both 1111 and 1112. so for example, another column in the report is titled cash.  I need cash to pull in for client 1 from both portfolios (1111 + 1112) but then for eligible amount, i only need the value from portfolio code 1111.  

@gmasta1129, two ways to achieve what you're after is:

 

1. Create a unique key that merges Client ID and Post Code, OR

2. Use MAX to return the maximum amount for the respective Client ID.

 

Firstly, though, I recommend you attempt the earlier options I provided so that assumptions are tested for fact and proven not to work.

 

All the best with your challenge.

 

Theo 🙂

 

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

TheoC
Super User
Super User

Hi @gmasta1129 

 

Do the following:

 

1. Click on Transform Data in the Home tab of the Power BI ribbon.

2. Click on the column you want to remove duplicates.

3. Right click on the column title and click "Remove Duplicates"

TheoC_0-1643847997983.png

 

Hope this helps 🙂

 

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors