Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Ignore Existing Relationship

My Lead table contains 2 dates, Open date & Close date.  Currently I have an active relationship between the Close date and a date table for other reports.

 

I'm trying to create a table showing the number of leads that are either open, won or lost & the amount of the won/lost based on the Open date.  When I try to bring in the amount won with Calculate([Amount], [won/lost] = "won"), the results are blank.  I figured out it's due to the existing active relationship between the Close date and a date table.

Is it possible to create a DAX formula to ignore the Close date and a date table relationship?

 

Thanks!

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Ok, got it. If you just want to remove the relationship, you can wrap your calculation with a CALCULATE function and CROSSFILTER function.CROSSFILTER can be used to remove the active relationship by mentioning the filter direction as none, this retains the other existing filters. Based on your exact need, you can explore how this option can be used in your case.

     

    something like : CALCULATE( <calculation>, CROSSFILTER(dt1,dt2,none)

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    You can use USERELATIONSHIP() function to force the PowerBI to use any relationship. For ex, if you want to use the inactive relationship between the Open date and date from date table, you can use USERELATIONSHIP(open_date,date_from_date_table).

     

    Thanks

    Raj

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thanks Anonymous 

       

      Currently I don't have an inactive relationship set up.  Is there a way to do it without setting an inactive relationship?  I tried it and it messed up a bunch of other reports.  If that's the only way to do it, I'll try and set up the other reports differently.

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Ok, got it. If you just want to remove the relationship, you can wrap your calculation with a CALCULATE function and CROSSFILTER function.CROSSFILTER can be used to remove the active relationship by mentioning the filter direction as none, this retains the other existing filters. Based on your exact need, you can explore how this option can be used in your case.

         

        something like : CALCULATE( <calculation>, CROSSFILTER(dt1,dt2,none)