topic Re: Userelationship not working between fact tables in DAX Commands and Tips

Userelationship not working between fact tables

dgurgel — Mon, 10 May 2021 12:40:00 GMT

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?

Re: Userelationship not working between fact tables

amitchandak — Mon, 10 May 2021 13:09:33 GMT

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

First of check why this relationship is inactive ?

Re: Userelationship not working between fact tables

dgurgel — Mon, 10 May 2021 15:09:20 GMT

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

Re: Userelationship not working between fact tables

PaulOlding — Mon, 10 May 2021 16:45:26 GMT

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.

Re: Userelationship not working between fact tables

daxer-almighty — Mon, 10 May 2021 17:23:55 GMT

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).