topic Re: Two inactive relationships between Customer table and Date table not working in DAX Commands and Tips

Two inactive relationships between Customer table and Date table not working

BudMan512 — Thu, 28 Sep 2023 16:47:34 GMT

Hey,

I have both Customer and Sales tables that I need to have relationships with the Date table.

The Customer table needs two relationships with the Date table for Gain Date and Loss date. The Sales table needs a relation with the date table as well.

So far this is the closest model I have. But it isn't working correctly yet.

Here is my report containing a sales table as well as information about the Gains and Losses of Customers.

Strangely enough the total Losses is correct but the Total Gains are way off. It should be 29. I have tried all the different combinaions of active and inactive relationships as well as DAX but can not get the final piece.

Here is a link to the report. I would appreciate some help with this.

Thanks

https://ruralcomputers-my.sharepoint.com/:u:/g/personal/bingraham_rccbi_com/EScqOFoa5uxNsgH68oSEiNIBEGyoURdap6gZ1mFXbwI2OA?e=szmSds

Re: Two inactive relationships between Customer table and Date table not working

ChiragGarg2512 — Fri, 29 Sep 2023 08:11:00 GMT

BudMan512 , Explain the relationships between tables and how those relationships are working.

Re: Two inactive relationships between Customer table and Date table not working

BudMan512 — Fri, 29 Sep 2023 12:50:00 GMT

I am attempting to use one Date table for connecting to the Sales table and Customer table. The Date table has an active relationship with Sales. The Customer table is connected with two inactive relationships so I can count the Gain Dates and Loss Dates. This arrangement is not working so I am looking for a way to make it work. I am open to suggestions.

Here is the DAX for the two inactive relationships that I am having a problem with.

Total Gains = COUNTA(Customer[Gain Date]) (I don't know how to make this relationship Active)

Total Losses = CALCULATE([Total Gains], USERELATIONSHIP(Customer[Loss Date],'Date'[Date])) * -1

Thanks for taking the time to reply.

Re: Two inactive relationships between Customer table and Date table not working

BudMan512 — Mon, 02 Oct 2023 13:30:04 GMT

I found the answer which uses the function TREATAS. First I removed the inactive relationships. I then used the following DAX, which worked.

Gains = CALCULATE([Count of Customers],

TREATAS(VALUES('Date'[Date]),Customer[Gain Date]))

Loss = CALCULATE([Count of Customers],

TREATAS(VALUES('Date'[Date]),Customer[Loss Date])) *-1

Net Gain/Loss = [Gains]+[Loss]