Ignore Existing Relationship

[Anonymous]

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!

[Anonymous]

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]

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.

[SouravJha]

Yes. You can use TREATAS function to setup virtual relationship that can be used for filtering. 

[Anonymous · Accepted solution]

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)

[Anonymous]

Thanks @Anonymous 

I haven't used CROSSFILTER before, but going to give it a try!