Forum Discussion

Mer333's avatar
Mer333
Regular Visitor
9 years ago
Solved

How to calculate a sequence?

Hello everyone!   Just recently I tried to use Power BI again after a year of delay and I stuck with a rare issue.   I need to calculate a sequence of hired people.   There are 3 columns Week ...
Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    9 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
7 years ago

I also wont to make a sequence column each ID No.. What I want to make is more simle than his case.

The ID Column is on tha table named "table1". For example, if ID"32437990" is at this column 3rd, I want to give first "32437990" the "1" sequence NO., 2nd this the "2" and 3rd this the "3".

 

 It also doesn't matter where to solve the problem, in DAX or M.

 

Please your advice .