Forum Discussion
PowerBI Model time dependant
Hi PGG79 ,
Thank you for reaching out to Microsoft Fabric Community.
Thank you lbendlin and SundarRaj for the response.
I have worked on your sample data and attached the PBIX file with the results. Please review it and let us know if it is helpful. If you have any further questions, please provide more details.
If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!
Thank you!!
When thinking of how to model the data you've shared, I would identify your distinct dimensions, pull them out, and construct the applicable bridge tables needed to define their interrletionships. Doing so would most likely result in a constellation model (star schema with multiple facts).
Taking your original data tables, I constructed the below model (Dates is marked as a date table, all top tables are dimensions, bottom tables are facts):
Which is a compoiste of the below three star schemas:
With the above model, something like the below is relatively easy:
Where the only DAX we have to write is for the measure:
Tech Hours = CALCULATE( SUM( TechHours[Hours] ), TechOrgDates, TechCalDates )
If you have any many-to many relationships (e.g., a tech could have two active calendars or be in two positions at the same time), then you should probably stick to a model like this.
If, though, for any given tech and date, you will only have one associated calendar and one associated org/position, then you can simplify the model by moving the foreign keys of your dimensions directly onto your main fact (TechHours). You can then directly relate all your dimensions to the one fact, like so:
With that set up, then you would no even need to write any DAX for the visual example I used before, the implicit sum on TechHours[Hours] works:
Follow up, to show more details of how exactly I transformed your original tables into the model(s) I shared. I did all the transformation/construction in Power Query. It would be possible to do all this in DAX, too, but then you'll have duplicated data loaded into your model.
Here is all the M for the seven tables above.
Calendars
let
Source = WorkingSchedule,
SelectCols = Table.SelectColumns(
Source, {"WorkingSchedule", " MoHrs", "Wk.hrs.", "Hrs/Day", "WkDys", "AnnualHrs"}
),
Distinct = Table.Distinct(SelectCols, {"WorkingSchedule"}),
AddId = Table.AddIndexColumn(Distinct, "ID", 1, 1, Int64.Type),
MoveIdFirst = Table.ReorderColumns(
AddId, {"ID", "WorkingSchedule", " MoHrs", "Wk.hrs.", "Hrs/Day", "WkDys", "AnnualHrs"}
)
in
MoveIdFirst
Dates
let
Source = RecordedHours[Recording Date],
MinDate = List.Min(Source),
MaxDate = List.Max(Source),
Dates = List.Dates(
Date.StartOfYear(MinDate),
Duration.Days(Date.EndOfYear(MaxDate) - Date.StartOfYear(MinDate)) + 1,
#duration(1, 0, 0, 0)
),
ToTable = Table.FromColumns(
{Dates},
Type.AddTableKey(type table [Date = date], {"Date"}, true)
),
AddYear = Table.AddColumn(ToTable, "Year", each Date.Year([Date]), Int64.Type),
AddQuarter = Table.AddColumn(AddYear, "Quarter", each Date.QuarterOfYear([Date]), Int64.Type),
AddMonthNo = Table.AddColumn(AddQuarter, "MonthNo", each Date.Month([Date]), Int64.Type),
AddMonth = Table.AddColumn(AddMonthNo, "Month", each Date.MonthName([Date]), type text),
AddYearMonthNo = Table.AddColumn(
AddMonth,
"YearMonthNo",
each [Year] * 100 + [MonthNo],
Int64.Type
),
AddYearMonth = Table.AddColumn(
AddYearMonthNo,
"YearMonth",
each Text.From([Year]) & "-" & Text.Start([Month], 3),
type text
)
in
AddYearMonth
Technicians
let
Source = TechnicianDetails,
SelectTechCols = Table.SelectColumns(Source,{"Technician", "Technician name"}),
DistinctTech = Table.Distinct(SelectTechCols, {"Technician"}),
AddId = Table.AddIndexColumn(DistinctTech, "ID", 1, 1, Int64.Type),
MoveIdFirst = Table.ReorderColumns(AddId,{"ID", "Technician", "Technician name"})
in
MoveIdFirst
OrgPositions
let
Source = TechnicianDetails,
SelectOrgCols = Table.SelectColumns(Source,{"Organization", "JobPosition", "CostArea"}),
Distinct = Table.Distinct(SelectOrgCols),
AddId = Table.AddIndexColumn(Distinct, "ID", 1, 1, Int64.Type),
MoveIdFirst = Table.ReorderColumns(AddId,{"ID", "Organization", "JobPosition", "CostArea"})
in
MoveIdFirst
TechCalDates
let
Source = WorkingSchedule,
MergeTechs = Table.NestedJoin(
Source, {"Technician"},
Technicians, {"Technician"},
"Technicians", JoinKind.Inner
),
MergeCals = Table.NestedJoin(
MergeTechs, {"WorkingSchedule"},
Calendars, {"WorkingSchedule"},
"Calendars", JoinKind.Inner
),
MaxDate = List.Max(Dates[Date]),
AddDates = Table.AddColumn(
MergeCals,
"Dates",
each List.Dates(
[Start Date],
Duration.Days(List.Min({MaxDate, [End Date]}) - [Start Date]) + 1,
#duration(1, 0, 0, 0)
),
type {date}
),
SelectCols = Table.SelectColumns(AddDates, {"Technicians", "Calendars", "Dates"}),
ExpandTechID = Table.ExpandTableColumn(SelectCols, "Technicians", {"ID"}, {"TechID"}),
ExpandCalID = Table.ExpandTableColumn(ExpandTechID, "Calendars", {"ID"}, {"CalID"}),
ExpandDates = Table.ExpandListColumn(ExpandCalID, "Dates")
in
ExpandDates
TechHours
let
Source = RecordedHours,
MergeTechs = Table.NestedJoin(
Source, {"Technician"},
Technicians, {"Technician"},
"Technicians", JoinKind.Inner
),
SelectCols = Table.SelectColumns(
MergeTechs,
{"Technicians", "Recording Date", "Hours", "HourType", "project", "Registered on"}
),
ExpandTechID = Table.ExpandTableColumn(SelectCols, "Technicians", {"ID"}, {"TechID"})
in
ExpandTechID
TechOrgDates
let
Source = TechnicianDetails,
MergeTechs = Table.NestedJoin(
Source, {"Technician"},
Technicians, {"Technician"},
"Technicians", JoinKind.Inner
),
MergeOrgPos = Table.NestedJoin(
MergeTechs,
{"Organization", "JobPosition", "CostArea"},
OrgPositions,
{"Organization", "JobPosition", "CostArea"},
"OrgPositions",
JoinKind.Inner
),
MaxDate = List.Max(Dates[Date]),
AddDates = Table.AddColumn(
MergeOrgPos,
"Dates",
each List.Dates(
[Start Date],
Duration.Days(List.Min({MaxDate, [End Date]}) - [Start Date]) + 1,
#duration(1, 0, 0, 0)
),
type {date}
),
SelectCols = Table.SelectColumns(AddDates, {"Technicians", "OrgPositions", "Dates"}),
ExpandTechID = Table.ExpandTableColumn(SelectCols, "Technicians", {"ID"}, {"TechID"}),
ExpandOrgPosID = Table.ExpandTableColumn(ExpandTechID, "OrgPositions", {"ID"}, {"OrgPosID"}),
ExpandDates = Table.ExpandListColumn(ExpandOrgPosID, "Dates")
in
ExpandDates
If your business rules allow for the simplified model I described (no need to support M:M), then you can disable the load on the two bridge tables, TechCalDates & TechOrgDates, and use the below in TechHours to move the foreign keys over. (Note there are probably more efficient ways to get the FKs here without first building out the bridge tables):
let
Source = RecordedHours,
MergeTechs = Table.NestedJoin(
Source, {"Technician"},
Technicians, {"Technician"},
"Technicians", JoinKind.Inner
),
SelectCols = Table.SelectColumns(
MergeTechs,
{"Technicians", "Recording Date", "Hours", "HourType", "project", "Registered on"}
),
ExpandTechID = Table.ExpandTableColumn(SelectCols, "Technicians", {"ID"}, {"TechID"}),
MergeCals = Table.NestedJoin(
ExpandTechID, {"TechID", "Recording Date"},
TechCalDates, {"TechID", "Dates"},
"TechCalDates", JoinKind.Inner
),
ExpandCalID = Table.ExpandTableColumn(MergeCals, "TechCalDates", {"CalID"}, {"CalID"}),
MergeOrgs = Table.NestedJoin(
ExpandCalID, {"TechID", "Recording Date"},
TechOrgDates, {"TechID", "Dates"},
"TechOrgDates", JoinKind.Inner
),
ExpandOrgID = Table.ExpandTableColumn(MergeOrgs, "TechOrgDates", {"OrgPosID"}, {"OrgPosID"})
in
ExpandOrgID