Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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
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
Read my blogs on
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 @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.
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
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
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
Read my blogs on
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.
@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:
Date | Dealer | Location | Chassis | Volume |
Jan | Peeters | Plant | 555321 | 1 |
Jan | Smith | Plant | 999231 | 1 |
Jan | Smith | HQ | 999231 | 0 |
Jan | Grève | Plant | 123456 | 1 |
Feb | Peeters | Plant | 555321 | 1 |
Feb | Smith | HQ | 999231 | 1 |
Feb | Smith | Plant | 999231 | 0 |
Feb | Grève | HQ | 123456 | 1 |
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.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
118 | |
101 | |
71 | |
61 |