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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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