Forum Discussion
Column with multiple checks
Hey guys,
I want to add a column with a calculation but i don't know the right formula to create it.
I want to check if the date in a cell matches the date in the next cell in the same column,
If this if true, i want to check if the matching cells have the same 'name of person' in the next column,
If this is true, i want to check which timestamp is the first one.
I then want to add the date value + test 1 as value in the new column
For the next cell, i want to ad the date value + test 2 as value in the column
Thanks in advance,
I was a little too fast in my example, that will sequence all events from same person regardless of the date.
To also consider sequence with each individual day you can alter the code to:
Sequence =
var curPerson = [Person]
var curTime=[TimeStamp]
var curDate = [TimeStamp].[Date]
return FORMAT([TimeStamp],"YYYY-MM-DD") & " test-" & (COUNTROWS(FILTER(Events, [Person] = curPerson && Events[TimeStamp].[Date] = curDate && Events[TimeStamp] < curTime))+1)
2 Replies
Hi,
When you say you want to check if a date matches the date in the "next cell" I guess you mean some other row in your table, right?In Power BI there is no such concept of next or previous rows since all there is to the engine is tables that consists of one or more columns.
To achive what you are trying to do you need to create some filter that returns the rows in your table that matches your conditions and from them you can evaluate the sequence of timestamps for those rows.
Take a look at the following simple example where I have created a column that will create a sequence for all rows with matching date and person
I was a little too fast in my example, that will sequence all events from same person regardless of the date.
To also consider sequence with each individual day you can alter the code to:
Sequence =
var curPerson = [Person]
var curTime=[TimeStamp]
var curDate = [TimeStamp].[Date]
return FORMAT([TimeStamp],"YYYY-MM-DD") & " test-" & (COUNTROWS(FILTER(Events, [Person] = curPerson && Events[TimeStamp].[Date] = curDate && Events[TimeStamp] < curTime))+1)