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.
Hi guys,
I need a new table that splits the lines of my time tracking table that tracks time of sickness and holiday.
In my time tracking table it is structured as so:
I need the lines to be split out depending on the duration so if the duration is 2 it will have 2 lines that are both 1 or if the duration is 3.5 it will be split out in 4 lines, 3 lines that are 1 and 1 line that is 0.5.
Name | Start Date | End Date | Duration |
John S | 08/12/2020 | 11/12/2020 | 1 |
John S | 08/12/2020 | 11/12/2020 | 1 |
John S | 08/12/2020 | 11/12/2020 | 1 |
John S | 08/12/2020 | 11/12/2020 | 0.5 |
This is how i want the data to come out.
We currently have this setup so it counts the days from Start Date to End Date but the duration stays the same for all lines. Current Data is below.
Name | Start Date | End Date | Duration |
John S | 08/12/2020 | 11/12/2020 | 3.5 |
John S | 08/12/2020 | 11/12/2020 | 3.5 |
John S | 08/12/2020 | 11/12/2020 | 3.5 |
John S | 08/12/2020 | 11/12/2020 | 3.5 |
We used a CROSSJOIN formula with the time tracking table and the calendar table:
Time Tracking Combined =
FILTER(CROSSJOIN('Time Tracking','Calendar'),'Calendar'[Date]>='Time Tracking'[Start].[Date]&&'Calendar'[Date]<='Time Tracking'[End].[Date])
What do i have to change so that the duration is split??
Solved! Go to Solution.
Does it have to be DAX?
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyFMIVtJRMjTSt9A3MjAyALKN9UyVYnWgkk7okkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Name " = _t, #"Start Date" = _t, Duration = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Start Date", type date}, {"Duration", type number}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Date", each List.Dates([Start Date],Number.RoundUp([Duration]),#duration(1, 0, 0, 0))),
#"Expanded Date" = Table.ExpandListColumn(#"Added Custom", "Date"),
#"Grouped Rows" = Table.Group(#"Expanded Date", {"Name "}, {{"Count", each _, type table [#"Name "=nullable text, Start Date=nullable date, Duration=nullable number, Date=date]}}),
#"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count],"Index",1,1)),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom1",{"Custom"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Name ", "Start Date", "Duration", "Date", "Index"}, {"Name ", "Start Date", "Duration", "Date", "Index"}),
#"Added Custom2" = Table.AddColumn(#"Expanded Custom", "Value", each if [Index] <= [Duration] then 1 else [Duration]+1-[Index]),
#"Removed Other Columns1" = Table.SelectColumns(#"Added Custom2",{"Name ", "Date", "Value"})
in
#"Removed Other Columns1"
This didnt help, it just replicates the tables i made in my post. I need to combine 2 tables in my power bi report and have the lines split if the duration is more than 1
Did you have problems running my code? It produces your expected outcome.
Yes it did work but i do not know how to make it applicable for my data
Sorry for the late reply @v-xulin-mstf
the picture of the table you attached is not my desired output.
The Deminsion has to add up to the duration.
So if John B's duration is 3, then he would get 3 seperate lines and in those lines, the dimension would be 1.
and if the Duration is greater than 1 and also has a 0.5 decimal, it should have an additonal line that is 0.5.
Hi @paulfink
I got you, but it is not supported with dax now, the demo I provided is a workaround.
Best Regards,
Link
glad you understand.
how are we going to go about this because ive been stuck
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 |
---|---|
114 | |
97 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |