Merge rows based on continuity

Question

Hi!&nbsp;How can I calculate a new table like this?&nbsp;Ages 19 and 20 are adjacent to each other in this filter context, so I want to lump them together in the background to reduce the number of iterations.&nbsp;&nbsp;

barritown · Answer

Hi&nbsp;Martin_Songstad,&nbsp;I can propose such a query:Result Table = 
VAR _temp = ADDCOLUMNS ( data,
                         "NewMin", COALESCE ( LOOKUPVALUE ( data[Min], data[Max], data[Min] ), data[Min] ),
                         "NewMax", COALESCE ( LOOKUPVALUE ( data[Max], data[Min], data[Max] ), data[Max] ) )
RETURN DISTINCT ( SELECTCOLUMNS (_temp, "Min", [NewMin], "Max", [NewMax] ) )If you have no more than 2 adjacent intervals, it should work.&nbsp;Best Regards, AlexanderMy YouTube vlog in EnglishMy YouTube vlog in Russian

martin_songstad · Answer

Thanks&nbsp;barritown&nbsp;!&nbsp;I am not getting excactly the result that I was looking for, but I will look into that code.&nbsp;&nbsp;

barritown · Answer

My solution will break if you try to merge three lines into one, like here:

Age	Min	Max
19	6575	7000
20	7000	7670
21	7670	8766

If your problem is of a different nature, you can try generating more toy data - maybe I'll come up with some other idea.

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

martin_songstad · Answer

Yes i would like to merge all continuously selected age groups.This is the best take I have come up with myself so far:EVALUATE

VAR __Simulated_selection =

FILTER(
	ALL(Aldersgrupper[Alder]),
	Aldersgrupper[Alder] = 19 || Aldersgrupper[Alder] = 20 || Aldersgrupper[Alder] = 22
	)

VAR __MinValues =
CALCULATETABLE(
CALCULATETABLE(
	SELECTCOLUMNS(Aldersgrupper,
					"Min", Aldersgrupper[Min],
					"Rank", RANKX(Aldersgrupper, [Min],,1)
					),
	EXCEPT( VALUES( Aldersgrupper[Min] ), 
			SELECTCOLUMNS(Aldersgrupper,
							"Max", Aldersgrupper[Max]) )
),
__Simulated_selection)

VAR __MaxValues =
CALCULATETABLE(
CALCULATETABLE(
	SELECTCOLUMNS(Aldersgrupper,
					"Max", Aldersgrupper[Max],
					"Rank", RANKX(Aldersgrupper, [Max],,1)
					),
	EXCEPT( VALUES( Aldersgrupper[Max] ), 
			SELECTCOLUMNS(Aldersgrupper,
							"Min", Aldersgrupper[Min]) )
),
__Simulated_selection)

VAR __AgeGroupsCompressed =
NATURALINNERJOIN(__MinValues,__MaxValues )

RETURN __AgeGroupsCompressed

barritown · Answer

I have an idea, which works on the test dataset below, but with my current [DAX] knowledge I am unable to pack it up into one single query.&nbsp;Take a look, maybe you will be able to achieve it.&nbsp;Test dataset:&nbsp;&nbsp;Step 1.&nbsp;We put all the minimums and maximums into one table, mark (1 - max, 0 - min in Status) and rank them.&nbsp;&nbsp;Step 2 (this is the main obstacle why I cannot pack it up into a single query).We add a calculated column which flags the first minimum and last maximum in each sequence.&nbsp;Step 3.&nbsp;&nbsp;We produce an output by filtering, re-ranking and joining tables.&nbsp;For convenience, here are those tables and column in the text format:one_column_view = 
VAR _tmp = UNION (
ADDCOLUMNS ( SELECTCOLUMNS ( data, "Value", data[Min] ), "Status", {0} ), 
ADDCOLUMNS ( SELECTCOLUMNS ( data, "Value", data[Max] ), "Status", {1} ) )
VAR _tmp2 = ADDCOLUMNS ( _tmp, "id", RANKX ( _tmp, [Value], , 1, Dense ) )
RETURN _tmp2flag = 
VAR cur_id = [id]
VAR part1 = IF ( one_column_view[Status] = 0 &amp;&amp; one_column_view[Status] &lt;&gt; CALCULATE ( MAX ( one_column_view[Status] ) , ALL ( one_column_view ), one_column_view[id] = cur_id - 1 ) || [id] = 1, 1, 0 )
VAR part2 = IF ( one_column_view[Status] = 1 &amp;&amp; one_column_view[Status] &lt;&gt; CALCULATE ( MAX ( one_column_view[Status] ) , ALL ( one_column_view ), one_column_view[id] = cur_id + 1 ), 1, 0 )
RETURN part1 + part2result = 
VAR _tmp = CALCULATETABLE ( one_column_view, one_column_view[flag] = 1 )
VAR _tmp2 = SELECTCOLUMNS ( _tmp, "Value", [Value], "Rank", RANKX ( _tmp, [Value], , ASC ), "Status", [Status] )
VAR _mins = SELECTCOLUMNS ( FILTER ( _tmp2, [Status] = 0 ), "Min", [Value], "Rank", INT ( [Rank] / 2 ) + 1 )
VAR _maxs = SELECTCOLUMNS ( FILTER ( _tmp2, [Status] = 1 ), "Max", [Value], "Rank", INT ( [Rank] / 2 ) )
RETURN NATURALINNERJOIN ( _mins, _maxs )&nbsp;Best Regards, AlexanderMy YouTube vlog in EnglishMy YouTube vlog in Russian

Forum Discussion

Merge rows based on continuity

5 Replies

Related content

Group By not continuously applying

MERGE QUERIES

Async merge risk

Merge columns based on condition

Spark Structured Streaming Continuous mode