Creating a conditional sequence in power BI table

[NitinReja]

Hi All,

I am a new user to Power BI... transitioning from Excel.

I have a table of visits done by our employees to certain addresses. However, in certain visits the items picked up are more than 1, hence, the number of rows are multiple if there are multiple item pickups in same visit. Each row represents one item.

Our charging is based on pickup as well as item... for Eg, every visit - first item is charged as Rs 300 whereas 2nd item onwards on same visit is charged at Rs 150 per item.

In excel, we could do this easily by combining, date-employee-adress to create a unique visit and then look at first row as item 1 and next similar rows as item 2... thereby allowing pricing (1 is charged at 300 whereas 2 is charged as 150). I have created Ref. Sequence column using IF formula with condition on column "Unique". How can I replicate somethint like this in Power BI?

[NitinReja]

Thanks @amitchandak ... it worked for the most part... It provides the count of the rows with commonality.... But I still have to figure out how to provide a sequence numbering of 1 & 2 as provided in the screenshot in my original query

[amitchandak · Accepted solution]

@NitinReja , A new column like

countx(filter(Table, [Employee] = earlier([employee]) && [Address] = earlier([Address]) && [Date] <= earlier([Date])),[Employee])

In case dates are same , then add an index column in power bi and use that in place of date