Active and Inactive relationships creating issues when filtering tables.

[JuanOchoa]

I am getting strange results when using two tables connected with an active and an inactive connection.

Table 1: Calendar Date

Table 2: Opportunities with three relevant columns "Opportunities[Created Date],  Opportunities[Closed Date], Opportunities[Opportunity Type]

and the two relationships between them

As part of a long calculation I am trying to find the last opportunity created in a certain period (the period will be selected on a visual by the user using Calendar[Date]).

My code:

VAR MaxDate =

      CALCULATE (

          MIN ( MAX ( Opportunities[Created Date] ), TODAY () ),      // To avoid errors in case there are created dates after today

         USERELATIONSHIP('Calendar'[Date], Opportunities[Created Date])

      )

VAR MinDate =

     DATE(2017,04,01)

// a table with all opportunities whose created date is between MaxDate and MinDate.

// I use this table in the next variable to filter the Opportunities table when

// calculating the maximum created date.

VAR ActiveOps =          

     FILTER (

         'Opportunities',

         ('Opportunities'[Created Date] <= MaxDate)

         && (Opportunities[Created Date] >=MinDate)

         && (Opportunities[Opportunity Type] = "New Business")

     )

// simple filter to get the maximum created date in the selected period.

VAR LastOppUptoMaxDate =

     CALCULATE (

         MAX(Opportunities[Created Date]),

         ActiveOps

     )

RETURN LastOppUptoMaxDate

To test: I set up filters on the page for (Calendar[Month] = 4) && (Calendar[Year] = 2018) to simulate the user using a slicer ( I actually have a slicer, I didn't want to complicate this post even further)

The results:  on the left what the measure above returns and on the right the content of the Opportunities table.

Why does my formula return 4/18 as the maximun date when there are opportunities created up to 4/30?

I am almost certain that the issue is the combination of active and inactive relationships between the tables, but I haven't been able to figure out why. It's driving me insane. 

Thanks for any advice,

Juan

[JuanOchoa · Accepted solution]

Solved (Sort of)

I surrounded the whole thing with a 

Calculate(

     ...

     ...,

     USERELATIONSHIP (Calendar[Date], Opportunities[Created Date])

)

It now works, but I think this is ugly and I'd love to understand which part of the original measure was failing.

[parry2k]

@JuanOchoa what does this variable return, can you put it in the return?

VAR MaxDate =
      CALCULATE (
          MIN ( MAX ( Opportunities[Created Date] ), TODAY () ),      // To avoid errors in case there are created dates after today
         USERELATIONSHIP('Calendar'[Date], Opportunities[Created Date])
      )

✨ Follow us on LinkedIn and  to our YouTube channel

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

[JuanOchoa]

With the page filters that I have, it returns 2018/04/30