CODEWITHSUNDEEP
powerbidaxdata-modeling
Paul
Paul

Reputation: 25

How to build a valid data model with indirect relationsship having 'null' values

I am trying to evaluate time entries for a project service organisation. I do have 3 Tables: Project, Project Task and Time-entry:

Project Task Time entry
Client *Project * *Project *
... Name *Task *
... ... ...

i do have a

So far so good. My problem now is, that some time entries do not have a task. So the task value there is 'null'.

enter image description here

When i want to sum the time-entries for a project, the entries with a 'null' in the task are missing since the relation is indirect and can't be traced from time-entry to project with the 'null' task.

What are my best options to handle this?

Activating the direct relation project --> time entry won't work, as i also need time entries per task. I was thinking of replacing the 'null' values by an arbitrary (?) task from the project, but I am unsure how to do this.

Any advice? Thanks.

Upvotes: 0

Views: 107

Answers (1)

Mark Wojciechowicz
Mark Wojciechowicz

Reputation: 4477

Make the relationship between Project and Time Entry, but keep it inactive. You can get project level metrics from the the Time Entry table by using USERELATIONSHIP:

CALCULATE (
    COUNTROWS('Time Entry'),
    USERELATIONSHIP ( 'Time Entry'[Project], 'Project'[Project] )
)

Upvotes: 1

Related Questions