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.
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))
Solved! Go to 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]
)
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]
)
Yay! Thanks for following up with your solution and glad my intermediary steps strategy works for you too @Zyg_D
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
@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.
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
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 |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |