Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
How can I get value for MAX timestamp and MIN timestamp in Table 1 from Table 2 as per below scenerio-
Solved! Go to Solution.
@learner03 apologies, I didn't catch that requirement in your initial description.
How about these:
MIN Timestamp =
VAR _USERNAME = Table1[UserName]
VAR _DATE = FORMAT(Table1[Date], "dd/mm/yyyy")
VAR _Result =
CALCULATE(
MIN(Table2[Timestamp]),
Table2[User] = _USERNAME,
FORMAT(Table2[Timestamp], "dd/mm/yyyy") = _DATE
)
Return
_Result
MAX Timestamp =
VAR _USERNAME = Table1[UserName]
VAR _DATE = FORMAT(Table1[Date], "dd/mm/yyyy")
VAR _Result =
CALCULATE(
MAX(Table2[Timestamp]),
Table2[User] = _USERNAME,
FORMAT(Table2[Timestamp], "dd/mm/yyyy") = _DATE
)
Return
_Result
@learner03 assuming you are trying to do this as a calculated column in Table 1, you could use something like:
MIN Timestamp =
VAR _USERNAME = Table1[UserName]
RETURN
CALCULATE(
MIN(Table2[Timestamp]),
Table2[User] = _USERNAME
)
and
MAX Timestamp =
VAR _USERNAME = Table1[UserName]
RETURN
CALCULATE(
MAX(Table2[Timestamp]),
Table2[User] = _USERNAME
)
Thanks . Just a follow up Question--
How can I get difference of the Start and end time in hours (like whole number example 5,6.5 etc)-
I tried with this, but getting minimum time from whole Table 2 and Max time from whole Table 2 like below-
where as I want min time of 21/07/201 and Max time of 21/07/201, and similarly min time of 22/07 amd max time of 22/07 and so on . So, basically min and max time of the date.
@learner03 apologies, I didn't catch that requirement in your initial description.
How about these:
MIN Timestamp =
VAR _USERNAME = Table1[UserName]
VAR _DATE = FORMAT(Table1[Date], "dd/mm/yyyy")
VAR _Result =
CALCULATE(
MIN(Table2[Timestamp]),
Table2[User] = _USERNAME,
FORMAT(Table2[Timestamp], "dd/mm/yyyy") = _DATE
)
Return
_Result
MAX Timestamp =
VAR _USERNAME = Table1[UserName]
VAR _DATE = FORMAT(Table1[Date], "dd/mm/yyyy")
VAR _Result =
CALCULATE(
MAX(Table2[Timestamp]),
Table2[User] = _USERNAME,
FORMAT(Table2[Timestamp], "dd/mm/yyyy") = _DATE
)
Return
_Result
User | Count |
---|---|
92 | |
85 | |
76 | |
66 | |
62 |
User | Count |
---|---|
110 | |
96 | |
95 | |
64 | |
57 |