Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Measure issues over data model

I am unable to create a calculation of how many hours a team is working on a project (based on the Cap Ex, Op Ex and Un Cat %) and therefore can't calculate cost.

Monthly Networking allows me to get the Total Networking Days per month.

Jira Team allows me to get their headcount and team rate by month

Jira Team Allocation gives me their % of time on a Project. A Project rolls up to PRoducts and Products have tags for finance breakdowns.
data model.png

The calculation is this:

Hours = CALCULATE(
(
8-(8*.123) // Hours per Day//
) *
'Monthly Networking Days'[Total Networking Days] * // Table has total days worked grouped by Month//
'Jira Team'[HeadCount] * // Table has a Scrum team head count grouped by Month//
CALCULATE(
'Jira Team Allocation'[% Cap Ex] +
'Jira Team Allocation'[% Op Ex] +
'Jira Team Allocation'[% Un Cat]
)) //Table has % by a Tag but I want the total percent. This table is itemized but does have a Month//

 

Everytime I try to create a measure I get these type of errors.

Measure error.png

I am sure it is because I don't have a one to one relationship from the main attribute table Jira Team Allocation.  Each month may have a scrum team allocated to several projects.    Each month the Team may have a different headcount and rate in the Jira Team.  And I can't use total monthly networking days because it wants to role it up for every line in the Jira Allocation table as well so if a Scrum Team has 3 projects for the month of July it will give me a total networking days of 63 instead on 21.  Same goes for the team rate and head counts.

3 REPLIES 3
Anonymous
Not applicable

@Anonymous  - The issues start with the model - You will need to separate dimension and fact tables. For example, you will need a separate Date and Scrum Team dimension tables. Then you can create relevant 1-many relationships between dimension and fact tables. 

I hope this helps. If it does, please Mark as a solution.
I also appreciate Kudos.
Nathan Peterson
Anonymous
Not applicable

Thanks I understand the dim vs fact table. Our data is just complex so it's not easy to simplify the data down 1-many relationships between dimension and fact tables

Anonymous
Not applicable

Yes, data modelling can be difficult, but complexity necessitates a dimensional model.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.