Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
I need a measure that will give me the duration since the previous timestamp for a particular person and category in the dataset. Below, I have a sample of the dataset. If it's the first timestamp for the given category and person, the duration should be blank or 0.
I figured it out using a calculated column for "Last TimeStamp", but I would prefer to only use measures as this dataset will have about 1M rows and I don't want more columns.
Solved! Go to Solution.
@itsme , Try a new column
New column =
var _maxx(filter(Table, [Name] = earlier([Name]) && [Category] =earlier([Category]) && [datetimestamp] < earlier([datetimestamp])), [datetimestamp])
return
[datetimestamp] -_max
Hi, @itsme
According to your description, you want to group according to [name] and [category], find the duration of previous timestamp, and you want to use measure to realize. Right?
Here are the steps you can follow:
(1)This is my test data:
(2)We can create a measure: “Duration”:
Duration =
VAR _cuurent_name =
SELECTEDVALUE ( 'Test'[Name] )
VAR _current_category =
SELECTEDVALUE ( 'Test'[Category] )
VAR _current_datetime =
SELECTEDVALUE ( 'Test'[DateTimeStamp] )
VAR _pre_date =
MAXX (
FILTER (
ALL ( 'Test' ),
'Test'[Name] = _cuurent_name
&& 'Test'[Category] = _current_category
&& 'Test'[DateTimeStamp] < _current_datetime
),
[DateTimeStamp]
)
VAR _duration =
VALUE ( _current_datetime - _pre_date )
RETURN
IF (
_pre_date = BLANK (),
BLANK (),
TRUNC ( _duration ) & ":"
& FORMAT ( _duration, "hh:mm:ss" )
)
(3)We put the measure [Duration] and other fields we need in the table, then we can meet your need:
If this method can't meet your requirement, can you share some specific input and output sample data?We can better understand the problem and help you.
Hi, @itsme
According to your description, you want to group according to [name] and [category], find the duration of previous timestamp, and you want to use measure to realize. Right?
Here are the steps you can follow:
(1)This is my test data:
(2)We can create a measure: “Duration”:
Duration =
VAR _cuurent_name =
SELECTEDVALUE ( 'Test'[Name] )
VAR _current_category =
SELECTEDVALUE ( 'Test'[Category] )
VAR _current_datetime =
SELECTEDVALUE ( 'Test'[DateTimeStamp] )
VAR _pre_date =
MAXX (
FILTER (
ALL ( 'Test' ),
'Test'[Name] = _cuurent_name
&& 'Test'[Category] = _current_category
&& 'Test'[DateTimeStamp] < _current_datetime
),
[DateTimeStamp]
)
VAR _duration =
VALUE ( _current_datetime - _pre_date )
RETURN
IF (
_pre_date = BLANK (),
BLANK (),
TRUNC ( _duration ) & ":"
& FORMAT ( _duration, "hh:mm:ss" )
)
(3)We put the measure [Duration] and other fields we need in the table, then we can meet your need:
If this method can't meet your requirement, can you share some specific input and output sample data?We can better understand the problem and help you.
@itsme , Try a new column
New column =
var _maxx(filter(Table, [Name] = earlier([Name]) && [Category] =earlier([Category]) && [datetimestamp] < earlier([datetimestamp])), [datetimestamp])
return
[datetimestamp] -_max
Hi @amitchandak, thanks for your reply. Do you know how to do this as a measure only without creating new columns?
User | Count |
---|---|
57 | |
48 | |
18 | |
16 | |
15 |
User | Count |
---|---|
116 | |
42 | |
41 | |
27 | |
21 |