Forum Discussion

BudMan512's avatar
BudMan512
Icon for Helper V rankHelper V
3 years ago
Solved

Two inactive relationships between Customer table and Date table not working

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

 

 

 

 

 

  • BudMan512's avatar
    BudMan512
    3 years ago

    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]

3 Replies

    • BudMan512's avatar
      BudMan512
      Icon for Helper V rankHelper V
      3 years ago

      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.
       
       
    • BudMan512's avatar
      BudMan512
      Icon for Helper V rankHelper V
      3 years ago

      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]