Forum Discussion
Group Rows by Relative Dates
Hi Anonymous ,
According to your description, here's my solution.
Create four calculated columns.
Column =
IF (
'Table'[Last Labor] = BLANK ()
|| MAXX (
FILTER ( 'Table', 'Table'[Index] = EARLIER ( 'Table'[Index] ) + 1 ),
'Table'[First Labor]
)
= BLANK (),
BLANK (),
IF (
DATEDIFF (
'Table'[Last Labor],
MAXX (
FILTER ( 'Table', 'Table'[Index] = EARLIER ( 'Table'[Index] ) + 1 ),
'Table'[First Labor]
),
DAY
) <= 30,
1
)
)
Column 2 =
IF (
'Table'[Column] <> 1,
BLANK (),
SUMX (
FILTER (
'Table',
'Table'[Index] <= EARLIER ( 'Table'[Index] )
&& MAXX (
FILTER ( 'Table', 'Table'[Index] = EARLIER ( 'Table'[Index] ) - 1 ),
'Table'[Column]
) <> 1
),
'Table'[Column]
)
)
Column 3 =
IF (
[Column 2] <> BLANK (),
[Column 2],
IF (
MAXX (
FILTER ( 'Table', 'Table'[Index] = EARLIER ( 'Table'[Index] ) - 1 ),
'Table'[Column 2]
)
<> BLANK (),
MAXX (
FILTER ( 'Table', 'Table'[Index] = EARLIER ( 'Table'[Index] ) - 1 ),
'Table'[Column 2]
)
)
)
Group Index =
IF (
[Column 3] <> BLANK (),
CONCATENATEX (
FILTER ( 'Table', 'Table'[Column 3] = EARLIER ( 'Table'[Column 3] ) ),
'Table'[Index]
),
IF ( 'Table'[Last Labor] <> BLANK (), CONVERT ( 'Table'[Index], STRING ) )
)
Get the result.
I attach my sample below for 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 v-yanjiang-msft ! Thank you so much for your response. I implemented the additional columns that you outlined above but I ran into a problem on column 2. When I add it to the table, loading the column takes approximately 4 hours... "Working on it". The dataset I'm applying it to is about 350,000 rows. Any ideas how the calculation time could be reduced?
Hi Anonymous ,
Actually for column2, does not use too complex operations, mainly using a sumx function, it's even simpler than other formulas. As your sample data is too large, it does run slow. But if other formula work normally but only column2 takes approximately 4 hours, I doubt it's a coincidence, the computer freeze etc.
Best Regards,
Community Support Team _ kalyjv-yanjiang-msft It is a simple operation, which is why I don't understand the slowness. Unfortunatelt, I can't reduce the size of my data.