USERELATIONSHIP Strange behavior !!!

[Anonymous]

Dear All,

I have an issue with the USERELATIONSHIP dax function, I have a blank result when I use it on my measures :

First of all this is my schema :

Then this is my measures :

AmountTotal_1 = CALCULATE(SUM ( Table1[amt] );DATESINPERIOD ( Date[Date]; MAX ( Date[Date] ); 10; YEAR );
USERELATIONSHIP ( Table1[ID]; Table2[ID] )
)

AmountTotal_2 = CALCULATE(SUM ( Table1[amt] );DATESINPERIOD ( Date[Date]; MAX ( Date[Date] ); 10; YEAR )
)

AmountTotal_3 = CALCULATE(SUM ( Table2[amount] );DATESINPERIOD ( Date[Date]; MAX ( Date[Date] ); 10; YEAR );
USERELATIONSHIP ( Table1[ID]; Table2[ID] )

This is the result :

 what looks strange is when I am using the same Measure with Table 2, it's working (AmountTotal_3)

Is there any problem with the granularity?

Is there any Function to add to avoid this?

Any Help please?

[Anonymous · Accepted solution]

When you activate the relationship between the two tables, the model becomes AMBIGUOUS. Please do not do this unless you want to have a hard time troubleshooting and create random numbers nobody will ever understand.

Please stick to this very simple rule of dimensional modeling:

Fact tables can only be joined through dimensions, never directly. The should also have all their columns hidden and measurements should be exposed only via explicit measures.

Try to violate this rule and you'll regret it rather sooner than later.

Best
D