Forum Discussion
How to calculate a sequence?
Hi Mer333,
To achieve your goal, only need to add some calculated columns to source table (in my test, it's named as Table1), please refer to below DAX formulas:
Previous Name for same ID = LOOKUPVALUE ( Table1[Name], Table1[Position ID], Table1[Position ID], Table1[Week], Table1[Week] - 1 ) flag = IF ( Table1[Previous Name for same ID] = Table1[Name], 0, 1 ) Number in sequence = CALCULATE ( SUM ( Table1[flag] ), ALLEXCEPT ( Table1, Table1[Position ID] ), Table1[Week] <= EARLIER ( Table1[Week] ) )Result output.
Best regards,
Yuliana Gu
Hi Mer333,
To achieve your goal, only need to add some calculated columns to source table (in my test, it's named as Table1), please refer to below DAX formulas:
Previous Name for same ID =
LOOKUPVALUE (
Table1[Name],
Table1[Position ID], Table1[Position ID],
Table1[Week], Table1[Week] - 1
)
flag =
IF ( Table1[Previous Name for same ID] = Table1[Name], 0, 1 )
Number in sequence =
CALCULATE (
SUM ( Table1[flag] ),
ALLEXCEPT ( Table1, Table1[Position ID] ),
Table1[Week] <= EARLIER ( Table1[Week] )
)Result output.
Best regards,
Yuliana Gu
Hi Yuliana!
Thank you so much! But it seems that problem expands a bit. I found two different names for the same id in some periods. Do you know how to exclude them from the calculation? Otherwise lookupvalue doesn't work. Thank you!
Hi Mer333,
In that case, how to display the sequence number? Could you please share some data?
Regards,
Yuliana GuHi Yuliana,
I've solved it by identifying duplicates in Power Query as how Ken described here -http://www.excelguru.ca/blog/2015/12/09/identify-duplicates-using-power-query/
Then I've filtered them out in LookUpValue. It's interesting though whether it's possible to identify duplicates in DAX.
Thank you!