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
Anonymous
Not applicable

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
SteveCampbell
Memorable Member
Memorable Member

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  
Read my blogs on  



View solution in original post

7 REPLIES 7
v-xicai
Community Support
Community Support

Hi @Anonymous ,

 

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.

zoloturu
Memorable Member
Memorable Member

Hi @Anonymous ,

 

@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 

SteveCampbell
Memorable Member
Memorable Member

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  
Read my blogs on  



Hi Steve, this solution ended up helping me out immensely, but I was wondering if you could possibly show how you would do the exact same thing but in power query instead of DAX?  Thanks!!!

Hi,

Share some data and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@SteveCampbellI'm trying to add another column and have the "volume" be based off of a text value.  I am getting an error that the DAX comparison operations do not support comparing values of type True/False with values of type Number.

 

The table:

DateDealerLocationChassisVolume
JanPeeters Plant5553211
JanSmith Plant9992311
JanSmith HQ9992310
JanGrève Plant1234561
FebPeeters Plant5553211
FebSmith HQ9992311
FebSmith Plant9992310
FebGrève HQ1234561

 

My DAX:

Volume =

VAR _index = 'Table'[Index]

VAR currentrowdate = 'Table'[Date]

VAR currentDealer = 'Table'[Dealer]

return

if( COUNTROWS(FILTER('Table',

   'Table'[Date] = currentrowdate

     && 'Table'[Dealer] = currentDealer

     && 'Table'[Location] <_index))>0,0,1)

 

Any help or insight is appreciated.

 

Hi @Anonymous 

 

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.

 

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.