topic Re: Relationship of balances with contracts in DAX Commands and Tips

Relationship of balances with contracts

brunoecr — Wed, 31 Aug 2022 20:21:45 GMT

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 ?

Re: Relationship of balances with contracts

daXtreme — Wed, 31 Aug 2022 21:23:05 GMT

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.

Re: Relationship of balances with contracts

brunoecr — Fri, 02 Sep 2022 11:36:04 GMT

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.

Re: Relationship of balances with contracts

v-easonf-msft — Mon, 05 Sep 2022 07:31:00 GMT

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

Re: Relationship of balances with contracts

Adescrit — Mon, 05 Sep 2022 07:56:14 GMT

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?

Re: Relationship of balances with contracts

daXtreme — Mon, 05 Sep 2022 07:58:46 GMT

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?

Re: Relationship of balances with contracts

brunoecr — Mon, 05 Sep 2022 12:21:12 GMT

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