Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Basically I need new column table1 with result of "compared table1 timestamp within the table2 start timestamp and endtime stamp"
Currently I tried and got error-
Fuction Used-
Solved! Go to Solution.
Hi @RAVICHANDRA ,
Modift it to:
Fuction Used- GetLS =
VAR _T =
ADDCOLUMNS ( 'Table2', "TS", EARLIER ( Table1[Timestamp] ) )
VAR _T2 =
ADDCOLUMNS (
_T,
"Count",
IF ( [TS] >= [StartTime] && [TS] <= [EndTime], 1, 0 )
)
RETURN
IF (
SUMX ( FILTER ( _T2, [ID] = EARLIER ( Table1[ID] ) ), [Count] ) = 0,
"Out",
"In"
)
Best Regards,
Community Support Team _ kalyj
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @RAVICHANDRA ,
According to your description, I modify the sample which contains time interval.
And here is Table1. As you can see, ID181 isn't in any time period in Table2, so it should be "Out". ID182 is in the first time period, so it should be "In".
Here's my solution, create a calculated column in Table1:
Fuction Used- GetLS =
VAR _T =
ADDCOLUMNS (
'Table2',
"TS", LOOKUPVALUE ( 'Table1'[Timestamp], Table1[ID], 'Table2'[ID] )
)
VAR _T2 =
ADDCOLUMNS (
_T,
"Count",
IF ( [TS] >= [StartTime] && [TS] <= [EndTime], 1, 0 )
)
RETURN
IF (
SUMX ( FILTER ( _T2, [ID] = EARLIER ( Table1[ID] ) ), [Count] ) = 0,
"Out",
"In"
)
Get the correct result:
I attach my sample below for your reference.
Best Regards,
Community Support Team _ kalyj
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Dear!
I tried this provoided solution and got error "A table of multiple values was supplied where a single value was expected."
I beleive this error not occurs to you because of your exampled Table1 doesn't have duplicate IDs, but my usecase have lot of different timestamps for each ID
apprciating your effort 👏
Thank You!
Hi @RAVICHANDRA ,
Modift it to:
Fuction Used- GetLS =
VAR _T =
ADDCOLUMNS ( 'Table2', "TS", EARLIER ( Table1[Timestamp] ) )
VAR _T2 =
ADDCOLUMNS (
_T,
"Count",
IF ( [TS] >= [StartTime] && [TS] <= [EndTime], 1, 0 )
)
RETURN
IF (
SUMX ( FILTER ( _T2, [ID] = EARLIER ( Table1[ID] ) ), [Count] ) = 0,
"Out",
"In"
)
Best Regards,
Community Support Team _ kalyj
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank You so much Dear! It's worked fine now, I just can say Your Excellent 🙂 Still not able to understand that fuction much, But working fine as I validated.
Thanks-
Ravi
@RAVICHANDRA , you have multiple values of dates per id in Table2. You need to either use some aggregation function (like MIN( 'Table2'[StartTime] ) ) or more columns to retrieve 1 value.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.
Check out my latest demo report in the data story gallery.
Stand with Ukraine!
Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/
Thank you!
Thanks ERD, But those cannot be aggregate as MIN MAX as throughout the clock in 24hrs randomly sometime will be IN and sometimes OUT, So aggregation is not possible I beleive. Sorry if you mean't anything more different then my understanding pls explain bit, Am basic level user 🙂
Hi @RAVICHANDRA ,
I noticed that each StartTimeUTC in the snapshot is continuous with the previous EndTimeUTC. So if we do it manually, for the timestamp in Table1, which value do you want it to compare with in Table2, the min StartTimeUTC (01-12-2022 10:00:00) and max EndTimeUTC(02-12-2022 10:24:52)?
Best Regards,
Community Support Team _ kalyj
Thanks for your input Dear! Attached table is for sample only and that has full time "IN; Usual data will not be the same always for my use case. There will time difference between previous end and next start stmaps many times usually.
Thank You Again- Ravi
Table2 example (Example has multiple duplicate IDs and those are required too), Headings PLDesc=ID.
in table2, are ID and EndTime/StartTime one-to-one?
User | Count |
---|---|
92 | |
85 | |
77 | |
66 | |
62 |
User | Count |
---|---|
110 | |
95 | |
95 | |
64 | |
59 |