Data model issue: Any way to link N:N that have NULLS as well?

[Anonymous]

Hey guys,

I have an issue related to my datamodel. 

What I want to reach is to SUM the RENTMAX.AmountRentMax and RENTLOSS.AmountRentLoss in a Bar Diagram next to each other, and I want to filter those two amounts simultaneously by the OBJECT.Type and OBJECT.Color.

The beginning is very simple:

Table OBJECT 1:n Table CONTRACTOBJECT via key ObjectID

Table CONTRACTOBJECT 1:n Table CONTRACT via key RentalObjectID

Table CONTRACT 1:n Table RENTMAX via key ContractID

However, I cannot seem to join the last table RENTLOSS

This is because not every ContractID has an AccountNum

And I know not every AccountNum has a ContractID.

On top of that some AccountNum have multiple ContractID's.

So it's clearly a N:N relationship.

Below is some example data that I want to connect to each other.

OBJECT
ObjectID	Type	Color
1	2	Blue
2	2	Green
3	1	Blue
4	1	Red
5	1	Blue

CONTRACTOBJECT
RentalObjectID	ObjectID
1	2
2	3
3	4
4	4

CONTRACT
ContractID	RentalObjectID	AccountNum
1	1	1
2	1	2
3	2	NULL
4	3	4
5	4	4
6	1	5
7	2	6
8	3	6
9	4	7
10	4	8

RENTMAX
ContractID	AmountRentMax	PeriodEnd
1	400	Saturday 31 March 2018
2	200	Saturday 31 March 2018
3	400	Saturday 31 March 2018
4	400	Saturday 31 March 2018
5	300	Saturday 31 March 2018
6	300	Saturday 31 March 2018
7	400	Saturday 31 March 2018
8	400	Saturday 31 March 2018
10	300	Saturday 31 March 2018
1	500	Wednesday 28 February 2018
2	300	Wednesday 28 February 2018
3	500	Wednesday 28 February 2018
4	500	Wednesday 28 February 2018
5	300	Wednesday 28 February 2018
6	300	Wednesday 28 February 2018
7	500	Wednesday 28 February 2018
8	500	Wednesday 28 February 2018
10	600	Wednesday 28 February 2018

RENTLOSS
AccountNum	AmountRentLoss	PeriodEnd
1	400	Saturday 31 March 2018
2	200	Saturday 31 March 2018
4	400	Saturday 31 March 2018
5	300	Saturday 31 March 2018
6	300	Saturday 31 March 2018
7	400	Saturday 31 March 2018
11	400	Saturday 31 March 2018
12	400	Saturday 31 March 2018
1	500	Wednesday 28 February 2018
2	300	Wednesday 28 February 2018
4	500	Wednesday 28 February 2018
5	300	Wednesday 28 February 2018
6	300	Wednesday 28 February 2018
7	500	Wednesday 28 February 2018
11	500	Wednesday 28 February 2018
12	500	Wednesday 28 February 2018

I tried to connect the RENTLOSS via a Bridge Table to RENTMAX with only 'PeriodEnd' in it. This way I am able to get both amounts in a Bar Diagram which reacts on the date correctly. Though I am unable to use filters based on the OBJECT table.

I know not all data will be filtered correctly, because an AccountNum without a ContractID won't be able to be filtered by dimensions from the OBJECT table, but this is not a problem, since the 'blanks' will then give me info where to increase the quality of the data,

I only care about the SUM and Date of the RENTLOSS.AmountRentLoss and RENTMAX.AmountRentMax, I don't care about the details behind the data (such as the ContractID's or AccountNums, etc.).

Maybe you guys have any suggestion I haven't thought of.

Kind regards,

Igor

[Stachu]

I'd use values from CONTRACT in slicer, and use the measures as follows:
as RENTMAX has join

MeasureRentMax = SUM(RENTMAX[AmountRentMax])

and as RENTLOSS doesn't have join

MeasureRentLoss =
CALCULATE (
    SUM ( RENTLOSS[AmountRentLoss] ),
    INTERSECT ( ALL ( RENTLOSS[AccountNum] ), VALUES ( CONTRACT[AccountNum] ) )
)

as for the dates I would create a new Calendar table and link it to both tables