Relationship of balances with contracts

[brunoecr]

Guys, can you help me?

I have 3 tables: dCalendar / dContracts / fSaldos

Relationships created:
- dCalendar(date) → dContracts(Contract date) - Active
- dCalendar(date) → fSaldos(dateSaldo) - Inactive
- dContracts(CodUnico) → fBalances(CodUnico) - Active

** The second relationship is inactive because it already has an indirect relationship

I need to bring the balance of each contract that evolves each month, and the contract date is always stipulated on the first day of the month, as well as the Balance.

The measure I'm using to bring the balance is as follows:
Balance = CALCULATE(SUM(fSados[Balance]), USERELATIONSHIP(dCalendar[date], fBalances[dateBalance]))

However, it deactivates the initial relationship of Date of Contracts with dCalendar, thus trasando all the balances of each month for not having a relationship of dates, could someone tell me if I can keep the previous filter of dates before activating the relationship by measure with the USERELATIONSHIP ?

[Adescrit · Accepted solution]

Hi @brunoecr ,

I would advise against having a relationship between a date dimension table (dCalendar) and an ordinary dimension table (dContracts).

If you remove the relationship betweendCalendar and dContracts, couldn't you then keep the relationship between dCalendar and fBalances active, and apply filters directly on the contract date in the dContracts table?

[brunoecr]

Hello. I ended up solving it by creating a table with the VALUES function only for contracts and making the relationship with fBalance, so dCalendar has no interference in the fBalance and fContracts tables

[daXtreme]

No, you can't because at any one time there can't be 2 active relationships between 2 tables (that's why your measure automatically disables one of the relationships). But you can have 2 date tables, for instance, one for each type of date. You can also move filters from one table to another using TREATAS.

[brunoecr]

Thanks for the answer, but when I make two tables of dates and I try to use the TEATRAS function, it has the same effect as the relationship, because TEATRAS creates a virtual relationship.

[daXtreme]

Can you please take a screenshot of your model and show us? Maybe there's a better way to structure the model and get rid of inactive relationships?

[v-easonf-msft]

Hi, @brunoecr 

If there is no relationship between the two tables, use Treatas  in measure to create a virtual relationship between the two tables to calculate the result .

If there is an inactive relationship between the two tables, you can use USERELATIONSHIP  in the formula to activate the inactive relationship to calculate the result.

Best Regards,
Community Support Team _ Eason