Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi, I have a table that looks like the below..
I want to show by each day, what the total number of calls was by user, and also the FIRST and LAST time a call was made per user.
My ideal output would be the below..
I have tried the MAX and MIN functions but they return the MAX time in my entire time dimension.. which isn't what I wanted. Any ideas how I can achieve the below?
Solved! Go to Solution.
Hi @jd8766
You can achieve this directly by using MIN and MAX:
MINTime = MIN('Table'[Time])
MaxTime = MAX('Table'[Time])
Then change the Format of the measures:
Output:
If your data is more complex, please try:
First Time = CALCULATE(MIN('Table'[Time]),FILTER(ALL('Table'),[Date]=MAX('Table'[Date])&&[User]=MAX('Table'[User])))
Last Time = CALCULATE(MAX('Table'[Time]),FILTER(ALL('Table'),[User]=MAX('Table'[User])&&[Date]=MAX('Table'[Date])))
Output:
If you need calculated column:
FIRSTTIME = CALCULATE(MIN('Table'[Time]),FILTER('Table',[User]=EARLIER('Table'[User])&&[Date]=EARLIER('Table'[Date])))
LASTTIME = CALCULATE(MAX('Table'[Time]),FILTER('Table',[User]=EARLIER('Table'[User])&&[Date]=EARLIER('Table'[Date])))
Output:
If you need a table:
Table 2 =
SUMMARIZE (
'Table',
'Table'[User],
'Table'[Date],
"Total Call",
SUM ( 'Table'[Calls] ),
"First Time",
MIN ( 'Table'[Time] ),
"Last Time",
MAX ( 'Table'[Time] )
)
Output:
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @jd8766
You can achieve this directly by using MIN and MAX:
MINTime = MIN('Table'[Time])
MaxTime = MAX('Table'[Time])
Then change the Format of the measures:
Output:
If your data is more complex, please try:
First Time = CALCULATE(MIN('Table'[Time]),FILTER(ALL('Table'),[Date]=MAX('Table'[Date])&&[User]=MAX('Table'[User])))
Last Time = CALCULATE(MAX('Table'[Time]),FILTER(ALL('Table'),[User]=MAX('Table'[User])&&[Date]=MAX('Table'[Date])))
Output:
If you need calculated column:
FIRSTTIME = CALCULATE(MIN('Table'[Time]),FILTER('Table',[User]=EARLIER('Table'[User])&&[Date]=EARLIER('Table'[Date])))
LASTTIME = CALCULATE(MAX('Table'[Time]),FILTER('Table',[User]=EARLIER('Table'[User])&&[Date]=EARLIER('Table'[Date])))
Output:
If you need a table:
Table 2 =
SUMMARIZE (
'Table',
'Table'[User],
'Table'[Date],
"Total Call",
SUM ( 'Table'[Calls] ),
"First Time",
MIN ( 'Table'[Time] ),
"Last Time",
MAX ( 'Table'[Time] )
)
Output:
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@jd8766 , If you create min/max measure thtat will work
max(Table[time])
min(Table[time])
if you need a column
minx(filter(Table, [User] = earlier([User]) ),[Time])
User | Count |
---|---|
101 | |
90 | |
80 | |
71 | |
70 |
User | Count |
---|---|
114 | |
99 | |
97 | |
73 | |
72 |