cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Helper IV

## 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/EScqOFoa5uxNsgH68oSEiNIB...

1 ACCEPTED SOLUTION
Helper IV

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 3
Impactful Individual

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

Helper IV

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]

Helper IV

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.

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors