Data Modelling

[tsarun1992]

Hello all,

My dataaset has two excel tables.

1. Timesheet entry table (NAV TABLE) - Here multiple resources will make entries daily and enter their time registration. So for a month, there will me approx 20+ entries per resource.

2. Available hours (AVL TABLE) - For every resource, per month, there will be only one entry which shows the total available hours for that resource in that month.

I have pulled in both these data to my PowerBI.

In both the table, the resources' "INITIALS" is the common column. 

So I have given Many to Many Relationshio (Both direction)

All the data in the top half section is from the NAV table and the bottom half is from the AVL table.

When I select a particular month from the NAV table, all the filters applies perfectly to the NAV data while in the bottom half you can see that the particular month's data is not getting filtered.
Instead, it takes data for all 9 Initials from the AVL table for the entire year.

Take as Example : For AMSH
For January Month

NAV Table - 112.5 Hours

AVL Table - 2,125 Hours (It takes data for AMSH for the entire year, But actually this must be 161.50 Hours for January Month)

Only when I select month from both the tables, the data is showing perfect (Can be seen below)

I'm unable to generate a relationship between Date / Month in both the tables.

Is there any solution to this?

[SanKing]

Hi @tsarun1992 ,
I would present it in a little bit different way and:

1. Create table with data:

Date = calendar(min(NAV Table[Month]),max(NAV Table[Month]))

2. Create measures for:

Quantity NAV, where 

Quantity = CALCULATE(SUM(NAV Table[Quantity]),FILTER(NAV Table,NAV Table[Month]>=MIN(Date[Month]) && NAV Table[Month]<=MIN(Date[Month])))

Net Work Hours AVL

Net Work Hours = CALCULATE(SUM(AVL Table[Net Work Hours]),FILTER(AVL Table,AVL Table[Month]>=MIN(Date[Month]) && AVL Table[Month]<=MIN(Date[Month]))) 

Month you can also replace with dates - depends what kind of data you have and how you want to show it (stable per month or with comparison current period to previous period).

[johnt75]

I would change the data model. Delete the relationship between NAV and AVL and create a new dimension table which has all the values from the initials column, e.g.

Dim Resources =
DISTINCT (
    UNION ( DISTINCT ( 'NAV'[Initials] ), DISTINCT ( 'AVL'[Initials] ) )
)

and then create one-to-many relationships from the new dimension table to both NAV and AVL. Use the initials column from the dimension table in any visuals or filters.

Also create a date table, properly marked as a date table, and link that to both NAV and AVL. Use columns from the date table in your filters and visuals.