Forum Discussion
Remove duplicates in a numeric column
I have a problem when creating a table in Power BI where showing the ID of a client/user duplicates the rows and I need to show the "last row", the problem is that being a numeric column that option is not there.
I attach here how the repeat customer number looks.
And when you right-click to show the last row, you don't see that option:
Do you have any idea how to display the last or first row of a found value?
lbendlin Thank you very much for your prompt reply.
For your question, here is the method I provided:
I noticed that the teamleader column and the reta_de_ventas column are changed in your data.
Therefore, I would suggest you to group the teamleader column according to it and sort the reta_de_ventas column to achieve displaying the last row of data.
Here's some dummy data
“Table”
Create measures.
Rank = VAR _RANK = RANKX( FILTER( ALL('Table'), 'Table'[teamleader] = MAX('Table'[teamleader]) ), CALCULATE(SELECTEDVALUE('Table'[reta_de_ventas])),,ASC,Dense ) RETURN _RANKMark = var _maxRank = MAXX( FILTER( ALL('Table'), 'Table'[teamleader] = MAX('Table'[teamleader]) ), [Rank] ) RETURN IF( [Rank] = _maxRank, 1, 0 )Filter values with a "Mark" of 1.
Here is the result.
Regards,
Nono Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
Sort in the way you need it sorted and then do a DISTINCT on that column.
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information or anything not related to the issue or question.
If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523He won't let me make a DISTINCT in the column because he says he expected a column and I gave him several.
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information or anything not related to the issue or question.
If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
lbendlin Thank you very much for your prompt reply.
For your question, here is the method I provided:
I noticed that the teamleader column and the reta_de_ventas column are changed in your data.
Therefore, I would suggest you to group the teamleader column according to it and sort the reta_de_ventas column to achieve displaying the last row of data.
Here's some dummy data
“Table”
Create measures.
Rank = VAR _RANK = RANKX( FILTER( ALL('Table'), 'Table'[teamleader] = MAX('Table'[teamleader]) ), CALCULATE(SELECTEDVALUE('Table'[reta_de_ventas])),,ASC,Dense ) RETURN _RANKMark = var _maxRank = MAXX( FILTER( ALL('Table'), 'Table'[teamleader] = MAX('Table'[teamleader]) ), [Rank] ) RETURN IF( [Rank] = _maxRank, 1, 0 )Filter values with a "Mark" of 1.
Here is the result.
Regards,
Nono Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
