Return row ID of Maximum overlapping days count from specific category

[Zyg_D]

This is my data: 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclSK1YlWcgKTzkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [employee = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"employee", type text}})
in
    #"Changed Type"

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fY5LDoAgDETvwppFPwq4VI9BuP81bOqgkhgWbSbw8qa1Bg7RZ7cREiIm7lEstliD2AN9GVs9bmAUnsM/uFAWeZXizDIwKEBUJWdWdD1MUrqjHQYmwXNOujI856SrDJ6frnYB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"type" = _t, employee = _t, date_start = _t, date_end = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"type", Int64.Type}, {"employee", type text}, {"date_start", Int64.Type}, {"date_end", Int64.Type}})
in
    #"Changed Type"

The goal is to add one column to table "employees" representing IDs from table "positions". Only one ID per employee. The correct ID is the one having "1" in the column "type" and having the maximum amount of days in the year 2020. This is the desired result: 

The calculation of days in the year 2020 for every position ID could look something similar to the following formula. But I don't know how to proceed. I do not want these numbers anywhere in tables, I only want one additional column in the table "employees". 

overlapping_days = 
var _year_start   = date(2020,01,01)
var _year_end     = date(2021,01,01)
var _period_start = date(left([date_start],4),mid([date_start],5,2),right([date_start],2))
var _period_end   = date(left([date_end],4),mid([date_end],5,2),right([date_end],2))
return
max(0,_year_end-_year_start-max(0,_period_start-_year_start)-max(0,_year_end-_period_end))

[Zyg_D · Accepted solution]

Thank you, @AllisonKennedy  . Following your suggestion to make intermediary steps visible I created intermediary table and was able to move further from this point. This was the first time I created VAR/ RETURN statements not in the beginning, but in the middle 🙂 I like that this works 🙂 

This was the intermediary table: 

And this is the final code for the column "position ID": 

position ID = 
VAR _tbl = SELECTCOLUMNS (
    positions,
    "ID", [ID],
    "type", [type],
    "employee", [employee],
    "overlapping_days",
        VAR _year_start = DATE ( 2020, 01, 01 )
        VAR _year_end = DATE ( 2021, 01, 01 )
        VAR _period_start = DATE ( LEFT ( [date_start], 4 ), MID ( [date_start], 5, 2 ), RIGHT ( [date_start], 2 ) )
        VAR _period_end = DATE ( LEFT ( [date_end], 4 ), MID ( [date_end], 5, 2 ), RIGHT ( [date_end], 2 ) )
        RETURN MAX (0, _year_end - _year_start - MAX(0,_period_start - _year_start) - MAX(0,_year_end - _period_end) )
)
RETURN MAXX(
    FILTER(
        _tbl,
        [employee] = employees[employee]
          && [type] = 1
          && [overlapping_days]= MAXX (
                                    FILTER (
                                        _tbl,
                                        [type] = 1
                                        && [employee] = employees[employee]
                                    ),
                                    [overlapping_days]
                                )
    ),
    [ID]
)

[AllisonKennedy]

Yay! Thanks for following up with your solution and glad my intermediary steps strategy works for you too @Zyg_D  

[AllisonKennedy]

First, why do you not want the extra column in Positions table? It can be hidden from report view and not shown in visualizations if that helps any.

You should be able to use MAXX though and keep going with variables to avoid needing to actually define the column in the Positions table.

Then use another MAXX(FILTER(Positions,

Use your column expression inside that filter expression, I usually write it out as separate columns first, then combine into one measure if needed.

I think a RANKX could help/be needed here as well.

[Zyg_D]

---

@AllisonKennedy wrote:
First, why do you not want the extra column in Positions table? It can be hidden from report view and not shown in visualizations if that helps any.

---

I do not need the column, because I will want to scale this to other years as well - not only 2020. The fireld for the year will be in the table "employees". So there will be different combinations of years and employees. It will be easy to take the year, because it will reside in the same table as the new column. So, since I know how to do it, I didn't want to put it into the sample. 

---

@AllisonKennedy wrote:
You should be able to use MAXX though and keep going with variables to avoid needing to actually define the column in the Positions table.

---

I struggle moving the variables to another table... from 'positions'[overlapping_days] to 'employees'. When I paste them into the new column calculation in the 'employees' table, I get errors. 

[AllisonKennedy]

Thanks @Zyg_D
The year information is actually really important and helpful to know. Can you please update your sample data to include what that looks like?

For your variables, you must provide them a row context of the positions table, so you cannot define them until you are inside a table function or iterator like MAXX or FILTER. It's not straightforward, which is why I start by keeping them as separate columns and then mash them up into one measure at the end if needed, once I've wrapped my head around all the intermediary steps. That's just the way my brain works though, maybe not yours.

@Greg_Deckler may be able to provide another perspective on this problem or help give insight into how to easily understand variables and row context, which are two of the main hurdles you need to overcome.