topic Problem with Inactive Relationship in Desktop

Problem with Inactive Relationship

Anonymous — Thu, 11 Apr 2024 13:36:50 GMT

Hello, I am working with records/rows that have two different dates, open date and close date. I want to show the counts of open and closed dates by month on a single table. Therefore, I created relationships with the open and closed dates to a date table. The open date has an active relationship with the date table and the closed date has an inactive relationship with the date table. The open date counts are displayed by month as expected, using the month from the date table in the matrix column and the formula =COUNT('Table'[Open Date]). However, the closed count is not displayed by month and instead the counts appear as having a blank month. The formula for the closed date is:

=CALCULATE(COUNT('Table'[Close Date]),USERELATIONSHIP('Calendar'[Date],'Table[Close Date])).

The resulting table is:

The closed count is not linked to a month on the date table when the inactive relationship is called with USERELATIONSHIP. The closed dates occur throughout January through April and should appear under those columns instead of the blank month column.

Thanks for your assistance

Re: Problem with Inactive Relationship

Greg_Deckler — Thu, 11 Apr 2024 13:41:37 GMT

Anonymous Try:

=CALCULATE(COUNT('Table'[Close Date]),ALL('Table'), USERELATIONSHIP('Calendar'[Date],'Table'[Close Date])).

Re: Problem with Inactive Relationship

Anonymous — Thu, 11 Apr 2024 14:24:32 GMT

Greg_Deckler thanks very much for this suggestion. I tried it and got the result below. It appears to have taken the total closed count and added it to each month.

Re: Problem with Inactive Relationship

Greg_Deckler — Thu, 11 Apr 2024 14:48:39 GMT

Anonymous Any chance you can post some sample data and maybe an image of your data model?

Re: Problem with Inactive Relationship

Anonymous — Thu, 11 Apr 2024 17:17:10 GMT

Thanks again for your reply - I figured out this issue was due to a data formatting error in the source data. Thank you again.