Forum Discussion
Calculated column to mark unique rows
I have (simplified) a table with Name and ID, which both can be duplicate. For filtering in my visuals I would like to have one row (counted) per unique id. So for example:
| Name | ID | Needed |
| Test 1 | 1500 | 1 |
| Test 1 | 1500 | |
| Test 1 | 1500 | |
| Test 2 | 1550 | 1 |
| Test 2 | 1550 | |
| Test 3 | 1570 | 1 |
So for every unique ID it only adds a 'one' to the first row it finds. For me it doesn't matter which duplicate row it marks with a one so no additional checks are needed. How can I create a new calculated column that does this? I tried the following code but then for the duplicate rows it just adds them up instead of showing 1x a one:
IsFirstUnique =
VAR CurrentID = [ID]
RETURN
CALCULATE (
COUNTROWS ( Gap_Table ),
FILTER (
ALL ( Gap_Table ),
[ID] = CurrentID
&& [Name] <= EARLIER ( [Name] )
)
)
With this code in my example for ID 1500 it shows in all the rows a 'three' but it should only show a one in the first row, and ignore the other duplicate 1500 rows.
Hi Roym ,
Please try below steps:
1. add a index column in Power Query Pane
2. create a new column with below dax formula
Column = VAR _name = [Name] VAR _id = [ID] VAR _index = [Index] VAR tmp = CALCULATETABLE ( VALUES ( 'Table'[ID] ), FILTER ( ALL ( 'Table' ), [ID] = _id && [Name] = _name ) ) VAR _a = COUNTROWS ( tmp ) VAR tmp1 = SUMMARIZE ( 'Table', [Name], [ID], "Min_idx", MIN ( [Index] ) ) VAR tmp2 = FILTER ( tmp1, [Name] = _name ) VAR _str = INT ( CONCATENATEX ( tmp2, [ID] + [Min_idx] ) ) RETURN IF ( _id + _index = _str, _a, BLANK () )Please refer the attached .pbix file
Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
Hi Roym ,
Please try below steps:
1. add a index column in Power Query Pane
2. create a new column with below dax formula
Column = VAR _name = [Name] VAR _id = [ID] VAR _index = [Index] VAR tmp = CALCULATETABLE ( VALUES ( 'Table'[ID] ), FILTER ( ALL ( 'Table' ), [ID] = _id && [Name] = _name ) ) VAR _a = COUNTROWS ( tmp ) VAR tmp1 = SUMMARIZE ( 'Table', [Name], [ID], "Min_idx", MIN ( [Index] ) ) VAR tmp2 = FILTER ( tmp1, [Name] = _name ) VAR _str = INT ( CONCATENATEX ( tmp2, [ID] + [Min_idx] ) ) RETURN IF ( _id + _index = _str, _a, BLANK () )Please refer the attached .pbix file
Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.This is perfect, thanks!!
Roym I'm not sure how you would accomplish that if all the rows are exactly the same. The issue being if they are all exactly the same, there is no way to determine when to return a value or not. You could add an Index in PQ and then you could accomplish it. Essentially use the Index to figure out which is the first duplicate and return a 1 in that case otherwise blank.
If that is not possible for some reason, can you provide more information about the intended use of this column? There may be other possible solutions. For example, if the ultimate use is to just get a distinct count of unique rows in your data, you could do this as a measure:
COUNTROWS(DISTINCT('Table'))
There is actually one column per ID that is different. It is called 'country. So for a similair ID the country will be different. Is it possible this way?
