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
Roym
Helper IV
Helper IV

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:

 

NameIDNeeded
Test 115001
Test 11500 
Test 11500 
Test 215501
Test 21550 
Test 315701

 

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.

1 ACCEPTED SOLUTION
v-binbinyu-msft
Community Support
Community Support

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 () )

vbinbinyumsft_0-1695975509865.png

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.

View solution in original post

4 REPLIES 4
v-binbinyu-msft
Community Support
Community Support

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 () )

vbinbinyumsft_0-1695975509865.png

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!!

Greg_Deckler
Super User
Super User

@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'))


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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?

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 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.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors