Forum Discussion
Date Table & Data Model question
Hey all,
First time post, sorry if my explaination isn't amazing as I've just been learning PowerBI for the past month or two. I'm currently setting up dashboards for my company for invoice forecasting and user performance, but I'm running into an annoying issue regarding ambiguous paths and my date table.
First off, because I'm new to data modeling I'm not 100% sure if I have 1 or 2 fact tables. I know for sure that my timesheets file is a fact table, it has all of my team's timesheet data and shows what client, project, deliverable etc.. every member clocks time to every day of the year.
The other important thing for us is our deliverables. Each deliverable has its own invoice forecast date, actual forecast date, KPIs, etc..
I want to be able to tell how much time each user clocks to each deliverable but I also need to know when the revenue for each deliverable is forecast for. I've put together the data model in the attached snip, but am unable to have the date table inform both the timesheets and the deliverables simultaneously without getting an ambiguous paths error.
More than happy to answer questions to get this resolved. Basically half my visuals work and half my visuals break when on of the circled paths is active, then vise versa when switching the active paths.
What do you want to measure for each table? Let's assume we use the following columns for some measures:
- Table = Timesheets, Column = Hours Actual- Table = Unique Deliverables, Column = Cost/HHP
The corresponding measures will be:
Hours = SUM('Timesheets'[Hours Actual]) Cost = CALCULATE(SUM(Cost/HHP), USERELATIONSHIP('2024-2025 Dates'[Date],'Unique Deliverables'[Invoice Forecast])For the first measure you don't need to do much since it has an active relationship with the Date table.
And you should be able to plot in a chart the 2nd measure using the measure as Y and '2024-2025 Dates Table'[Date] for X axis.
4 Replies
Heya,
When calculating the measures using the inactive relationship use the USERELATIONSHIP function. See the example below (where 'Date'[Date] active relationship is with a different column in the same table and 'Date'[Date] is in an inactive relationship with MTD[Date] which is the column I am interested in in this calculation.
MTD = CALCULATE ( SELECTEDMEASURE (), DATESMTD ( 'Date'[Date] ), USERELATIONSHIP ( 'Date'[Date], MTD[Date] )Hey! Ok so I'm not super familiar with measures yet. Would you be able to translate that generic solution into one using my datasets & columns? For reference these are the relationships I currently have from the original snip:
- Active: 2024-2025 Dates Table, column = Date -> Timesheets Table, column = Date
- Inactive: 2024-2025 Dates Table, column = Date -> Unique Deliverables Table, column = Invoice Forecast
Not sure if it's relevant or not, but for the invoice forecast date of the deliverables, I only care about the month it's forecast in. The actual day of the month is irrelevant.
What do you want to measure for each table? Let's assume we use the following columns for some measures:
- Table = Timesheets, Column = Hours Actual- Table = Unique Deliverables, Column = Cost/HHP
The corresponding measures will be:
Hours = SUM('Timesheets'[Hours Actual]) Cost = CALCULATE(SUM(Cost/HHP), USERELATIONSHIP('2024-2025 Dates'[Date],'Unique Deliverables'[Invoice Forecast])For the first measure you don't need to do much since it has an active relationship with the Date table.
And you should be able to plot in a chart the 2nd measure using the measure as Y and '2024-2025 Dates Table'[Date] for X axis.
