Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
dgurgel
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.

dgurgel_1-1620649582823.png


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:

dgurgel_3-1620650134652.png

 

How can I make this work?

4 REPLIES 4
daxer-almighty
Solution Sage
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).

PaulOlding
Solution Sage
Solution Sage

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.

amitchandak
Super User
Super User

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

First of check why this relationship is inactive ?

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors