cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Mer333 Frequent Visitor
Frequent Visitor

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  Position ID  Name

 

Position ID is stable, but names can vary from week to week for the same Position ID in case of Vacancy or other reasons.

 

I need to mark each name change as a new number in sequence for the particular Position ID. At the same time, names can be repeated (if there was a vacancy (1) for a few weeks, then a person (2) and a vacancy again (3)).

 

At this moment it doesn't matter where to solve the problem, in DAX or M.

 

I've uploaded an example here:

https://dl.dropboxusercontent.com/u/4601806/Calculating%20sequence.xlsx

 

Thank you very much in advance!

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft v-yulgu-msft
Microsoft

Re: 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.
3.PNG

 

Best regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
Microsoft v-yulgu-msft
Microsoft

Re: 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.
3.PNG

 

Best regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Mer333 Frequent Visitor
Frequent Visitor

Re: How to calculate a sequence?

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!

Microsoft v-yulgu-msft
Microsoft

Re: How to calculate a sequence?

Hi @Mer333,

 

In that case, how to display the sequence number? Could you please share some data?

 

Regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Mer333 Frequent Visitor
Frequent Visitor

Re: How to calculate a sequence?

Hi 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!

127029 Occasional Visitor
Occasional Visitor

Re: How to calculate a sequence?

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 .

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)