cancel
Showing results for
Did you mean:
Regular Visitor

## Userelationship not working between fact tables

Hello,

I've got the following scheme, where the tables RESERVA_INVESTIMENTO and WALLET_OPERACOES are both fact tables. However, it is possible to use RESERVA as a dimension table for WALLET.
The relationship is inactive because it causes ambiguity between the relationships marked with a cross.

But when I use a formula like this, for example:

``````Measure =
CALCULATE(SUM('RESERVA_INVESTIMENTO'[VALUE]),
USERELATIONSHIP(
'RESERVA_INVESTIMENTO'[ID_RESERVA],
'WALLET_OPERACOES'[ID_RESERVA]))``````

I get a report like this:

How can I make this work?

4 REPLIES 4
Solution Sage

I'll give you the best piece of advice you could get in this world with this model: CHANGE THE MODEL since it's simply incorrect and rather sooner than later you'll be sorry you have not taken this advice seriously (in case you don't).

Super User

The direction of the relationship between RESERVA_INVESTIMENTO and WALLET_OPERACOES is the reason you get the same value for Measure in every row of your visual.  WALLET_OPERACOES doesn't filter RESERVA_INVESTIMENTO so you get the total amount of SUM('RESERVA_INVESTIMENTO'[VALUE]).

As you say, you're using RESERVA_INVESTIMENTO  as a dimension for WALLET_OPERACOES, but then your measure is summing an amount on the dimension table.

You could potentially set the relationship direction to Both, but be careful as this can cause problems.

Super User

@dgurgel , USERELATIONSHIP is to activate inactive between a set active/inactive.

First of check why this relationship is inactive ?

Regular Visitor

It's inactive because it would cause ambiguity between the other dimensions relationships, that are feed both fact tables.  These need to be inactive for the circled relation to be active.
INVESTIDORES -> WALLET_OPERACOES
DimCalendario -> WALLET_OPERACOES
SOLICITACAO -> WALLET_OPERACOES