Forum Discussion

Martin_Songstad's avatar
Martin_Songstad
Frequent Visitor
3 years ago

Merge rows based on continuity

Hi!   How can I calculate a new table like this?   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 o...
barritown's avatar
barritown
Solution Sage
3 years ago

Hi Martin_Songstad,

 

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.

 

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

  • Martin_Songstad's avatar
    Martin_Songstad
    Frequent Visitor
    3 years ago

    Thanks barritown !

     

    I am not getting excactly the result that I was looking for, but I will look into that code.

     

     

    • barritown's avatar
      barritown
      Solution Sage
      3 years ago

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

      AgeMinMax
      1965757000
      2070007670
      2176708766

       

      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's avatar
        Martin_Songstad
        Frequent Visitor
        3 years ago

        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