Forum Discussion

JuanOchoa's avatar
JuanOchoa
Frequent Visitor
4 years ago
Solved

Active and Inactive relationships creating issues when filtering tables.

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

 

 

Need Help
  • JuanOchoa's avatar
    JuanOchoa
    4 years ago

    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.

3 Replies

  • parry2k's avatar
    parry2k
    Super User
    4 years ago

    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's avatar
      JuanOchoa
      Frequent Visitor
      4 years ago

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

  • JuanOchoa's avatar
    JuanOchoa
    Frequent Visitor
    4 years ago

    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.