Forum Discussion
Project Table linking to Dates Table
Hey, I'm having an issue with a Power Bi project I've been trying to do, I am not sure if there is a viable solution for this issue or not so any help would be greatly appreciated.
Pretext:
I have a projects table as such:
ProjectName Start Day End Day Estimate Work Hours Average Time Per Work Day*
and I have a dates Table that filters out saturdays and sundays, gets work days:
Date Year MonthName DayName
*Average time per work day uses a function dependant on the Dates table to find the work days between the start and end and divides the total hours by that number.
Issue:
So I have done everything in power Querry except using DAX CALCULATE(SUM(Average Per Work Day),FILTER([Date] >= 'Project'[Start Day] && [Date] <= 'Project'[End Day])) to make a column off the dates table with the work hours that day.
This works fine however the actual report I get is not linked between tables projects and dates, meaning when a project is selected in a table widget the graph showing the date vs hours per day does not rerflect the time for just that project. I was told DAX happens after querrying so theres no link made, ie the total hours is just a calculation and not comprised of rows in the other table, but I cant think of a logical relationship or solution in power Bi so that the Dates table and the projects table are linked and the report visuals reflect that when an item in the projects table ive made is clicked it adjusts the graph. Basically one way or another I need continous dates to graph and I think each date needs to own all the projects, maybe as a dynamic column making expression and account for the hours that project adds that day. Any Ideas how I would go about this?
3 Replies
Anonymous ,
Could you please show some sample data and clarify more details?
Regards,
Jimmy Tao
Here is my data:
Projects(in 2 pics, one table tho)
let
Source = Excel.Workbook(File.Contents("Requests.xlsx"), null, true),
Requests_Sheet = Source{[Item="Requests",Kind="Sheet"]}[Data],
#"Filtered Rows" = Table.SelectRows(Requests_Sheet, each ([Column1] <> null)),
#"Promoted Headers" = Table.PromoteHeaders(#"Filtered Rows", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Project Name", type text}, {"Day Drafting will Begin", type date}, {"Day Drafting Needed by", type date}, {"Total Number of Drawings", Int64.Type}, {"Overall Estimated Time(in hours)", type number}, {"Requester", type text}, {"Request Timestamp", type datetime}}),
#"Invoked Custom Function" = Table.AddColumn(#"Changed Type", "Work Days Between", each #"Work Days"([Day Drafting will Begin], [Day Drafting Needed by])),
#"Added Custom" = Table.AddColumn(#"Invoked Custom Function", "Average Time By Day(Hours)", each [#"Overall Estimated Time(in hours)"]/[Work Days Between]),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Average Time By Day(Hours)", type number}, {"Work Days Between", Int64.Type}})
in
#"Changed Type1"Dates:
Drafting Hours Today = CALCULATE(SUM(Requests[Average Time By Day(Hours)]),FILTER(Requests,Calendar[Date] >= Requests[Day Drafting will Begin] && 'Calendar'[Date] <= Requests[Day Drafting Needed by]))let
Source = List.Dates(#date(2019, 5, 1), 365, #duration(1, 0, 0, 0)),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
#"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([Date]), Int64.Type),
#"Inserted Month Name" = Table.AddColumn(#"Inserted Year", "Month Name", each Date.MonthName([Date]), type text),
#"Inserted Day Name" = Table.AddColumn(#"Inserted Month Name", "Day Name", each Date.DayOfWeekName([Date]), type text),
#"Filtered Rows" = Table.SelectRows(#"Inserted Day Name", each ([Day Name] <> "Saturday" and [Day Name] <> "Sunday")),
#"Inserted Date" = Table.AddColumn(#"Filtered Rows", "Date.1", each DateTime.Date([Date]), type date),
#"Renamed Columns1" = Table.RenameColumns(#"Inserted Date",{{"Date.1", "Date Only"}}),
#"Removed Columns" = Table.RemoveColumns(#"Renamed Columns1",{"Date Only"})
in
#"Removed Columns"Report:
So my goal is to use a relationship(I have none right now) to link the graph from dates and the table from projects so clicking one shows contribution from the other
Hopefully this is what you wanted, Thank you!
Anonymous ,
You need to build an addtional column in both project and date table. Then create relationshio between the two tables based on the new columns. Make sure one of the new column should contain unique value.
Regards,
Jimmy Tao
