Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
So from an existing table I was able to extract this :
The goal is to see by day how many time a person (name) spend on a process.
So for that I want to make a calculated column but then first I need to sort these 4 columns so
that per day, per name and per time I can see which process was worked on.
Because as you now see it it is not correct.
This is what the first name did on Feb 1st:
And also as you see here the sorting is not correct as time is all over the place.
Is there a way I can sort this new table?
So by date, then by name and then by time.
Solved! Go to Solution.
output
Column =
var currenttime = 'Table'[Time]
var prev_time =
SELECTCOLUMNS(
OFFSET(
-1,
SUMMARIZE(
'Table',
'Table'[Date],
'Table'[name],
'Table'[Time]
),
ORDERBY( 'Table'[Date] ,asc, 'Table'[Time] , asc),
PARTITIONBY('Table'[Name],'Table'[Date])
),"time",'Table'[Time]
)
return DATEDIFF(prev_time,currenttime,MINUTE)
Column 3 =
SELECTCOLUMNS(
OFFSET(
1,
SUMMARIZE(
'Table',
'Table'[Name],
'Table'[Date],
'Table'[Time]
,'Table'[Column]
),
ORDERBY( 'Table'[Date] ,asc, 'Table'[Time] , asc),
PARTITIONBY('Table'[Name],'Table'[Date])
),"time",'Table'[Column]
)
then use in the visual, column3
hope this helps .
If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠
Thanks @Daniel29195 this looks very impressive.
Although the partitionby seems to have an error but there is still output.
Put it now on my main table as there was no use for a new table.
Still these values are not entirely correct I guess:
I think the process Apply should show 74 minutes and the Folding Delivery 12 minutes etc.
And also the change in the day causes some problems.
Should 'reset' at a day level when calculating.
output
Column =
var currenttime = 'Table'[Time]
var prev_time =
SELECTCOLUMNS(
OFFSET(
-1,
SUMMARIZE(
'Table',
'Table'[Date],
'Table'[name],
'Table'[Time]
),
ORDERBY( 'Table'[Date] ,asc, 'Table'[Time] , asc),
PARTITIONBY('Table'[Name],'Table'[Date])
),"time",'Table'[Time]
)
return DATEDIFF(prev_time,currenttime,MINUTE)
Column 3 =
SELECTCOLUMNS(
OFFSET(
1,
SUMMARIZE(
'Table',
'Table'[Name],
'Table'[Date],
'Table'[Time]
,'Table'[Column]
),
ORDERBY( 'Table'[Date] ,asc, 'Table'[Time] , asc),
PARTITIONBY('Table'[Name],'Table'[Date])
),"time",'Table'[Column]
)
then use in the visual, column3
hope this helps .
If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠
@Daniel29195 thanks!
Unfortunately I get this on the first column:
Is it an exact copy as you have made in the pbix I sent?
@rpinxt it seems from the image, you ddidnt put the name of the column you are creating ,
you are starting your code with var =
@rpinxt is it possible to share the pbix file to take a look at what is happening from your side ?
🤦
You are right...sorry about that!
Looks indeed to be working.
Thanks a lot!!
Yes I uploaded the pbix file here:
https://drive.google.com/file/d/1nV-2HYrQPcUIDiaO8XbZbz--3zHY7qzE/view?usp=drivesdk
Let me know if you cannot access.
output
caclulated column added to the table :
Column =
var currenttime = 'Table'[Time]
var prev_time =
SELECTCOLUMNS(
OFFSET(
-1,
SUMMARIZE(
'Table',
'Table'[DateName],
'Table'[name],
'Table'[Time]
),
ORDERBY( 'Table'[DateName] ,asc, 'Table'[Time] , asc),
PARTITIONBY('Table'[name])
),"time",'Table'[Time]
)
return DATEDIFF(prev_time,currenttime,MINUTE)
let me know if this helps .
If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
105 | |
79 | |
69 | |
62 |
User | Count |
---|---|
143 | |
104 | |
103 | |
82 | |
70 |