cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
arvanvos Occasional Visitor
Occasional Visitor

Add column where the first duplicate value gets value 1 and the second duplicate gets value 2

Hello

 

I have a question concering duplicates and I hope someone can help me.

I would like to add a column (not in the query editor) where the first duplicate value receives a value 1 and the next duplicate receives value 0. So I can make a sum of the values and still keep de duplicate lines in my table.

 

I have a table with two columns named dealer and chassis

Dealer   Chassis

Peeters  555321

Smith    999231

Smith    999231

Grève    123456

 

The result would have to be:

Dealer    Chassis   Volume

Peeters   555321       1

Smith     999231        1

Smith     9999231      0

Grève     123456        1

 

Is this possible? Thank you in advance

 

Cheers

Arne

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User I
Super User I

Re: Add column where the first duplicate value gets value 1 and the second duplicate gets value 2

Note that unlike excel, there is no "order" in the tables of Power BI. You will need to have some sort of index to differentiate the rows.

 

You can add an index column in Power Query.

Click add Column > index Column

Annotation 2020-01-15 081949.png

 

Then, add the column below in DAX:

 

Volume = 
var _index = 'Table'[Index]
var _dealer = 'Table'[Dealer]

RETURN

if( COUNTROWS(
    FILTER('Table',
    'Table'[Dealer] = _dealer
    && 'Table'[Chassis] < _Index)
    ) > 0 , 0, 1)

 

 

Appreciate your Kudos
Connect with me!

Stay up to date on  linkedin-logo.png
Read my blogs on  powerbi.tips_.png



Did I answer your question? Mark my post as a solution! Proud to be a Super User!


Connect with me!

Stay up to date on  linkedin-logo.png
Read my blogs on  powerbi.tips_.png



View solution in original post

4 REPLIES 4
Highlighted
Super User I
Super User I

Re: Add column where the first duplicate value gets value 1 and the second duplicate gets value 2

Note that unlike excel, there is no "order" in the tables of Power BI. You will need to have some sort of index to differentiate the rows.

 

You can add an index column in Power Query.

Click add Column > index Column

Annotation 2020-01-15 081949.png

 

Then, add the column below in DAX:

 

Volume = 
var _index = 'Table'[Index]
var _dealer = 'Table'[Dealer]

RETURN

if( COUNTROWS(
    FILTER('Table',
    'Table'[Dealer] = _dealer
    && 'Table'[Chassis] < _Index)
    ) > 0 , 0, 1)

 

 

Appreciate your Kudos
Connect with me!

Stay up to date on  linkedin-logo.png
Read my blogs on  powerbi.tips_.png



Did I answer your question? Mark my post as a solution! Proud to be a Super User!


Connect with me!

Stay up to date on  linkedin-logo.png
Read my blogs on  powerbi.tips_.png



View solution in original post

Super User IV
Super User IV

Re: Add column where the first duplicate value gets value 1 and the second duplicate gets value 2

Hi @arvanvos 

 

You can try using power query as on the attached.

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

 

zoloturu
Advisor

Re: Add column where the first duplicate value gets value 1 and the second duplicate gets value 2

Hi @arvanvos ,

 

@SteveCampbell is correct about an order in DAX and that PowerQuery is better for such tasks. Anyway, if you need it in DAX, here is a solution:

 

Before adding a resulting column you need to add an extra one:

RAND = [Chassis] + RAND()

Then we are ready to finish by adding the column you want:

flag = 
VAR __dealer_CurrRow = [Dealer]
VAR __chassis_CurrRow = [Chassis]
VAR __tbl =
    FILTER (
        'Table',
        'Table'[Dealer] = __dealer_CurrRow
            && 'Table'[Chassis] = __chassis_CurrRow
    )
VAR __tbl2 =
    TOPN ( 1, __tbl, [RAND], ASC )
VAR __rnd =
    SUMX ( __tbl2, [RAND] )
    
RETURN
    IF ( __rnd = [RAND], 1, 0 )

That's it.

 

Regards,
Ruslan Zolotukhin (zoloturu)
BI Engineer at Akvelon Inc. / Kharkiv Power BI User Group Leader / DAX & Power BI Trainer
-------------------------------------------------------------------
Did I answer your question? Mark my post as a solution!
It was useful? Press Thumbs Up!

You are from Ukraine? If yes then welcome to Power BI User Group - KhPUG website. Other country? Check and find proper one - Power BI User Groups 

Community Support Team
Community Support Team

Re: Add column where the first duplicate value gets value 1 and the second duplicate gets value 2

Hi @arvanvos ,

 

You can create columns like DAX below.

 

Rank = CALCULATE(COUNT(Table1[Chassis]),FILTER(ALLSELECTED(Table1),Table1[Dealer]<=EARLIER(Table1[Dealer])))
 
Volume= IF([Rank]=1,1,0)

 

Best Regards,

Amy

 

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors