More efficient way to use inactive relationships to filter report

[nbs33]

I have built a report with visuals displaying data from two fact tables (T1 and T2) that are connected to a Date table. My model is very complex and I founded out there is a circular dependency created by the realtionship between one for the fact tables and the date table (see realtionship circled in red).

I can make this relationhip inactive and use USEREALTIONSHIP to fix this issue but I need to create a measure for each value in my visuals so that it filters properly by date.

Is there are way to do this more efficently? Can I write one measure to filter the entire visual or use a second date table the mirrors my original date table without an active realtionship? I apprreciate any input.

 

[tamerj1 · Accepted solution]

@nbs33 

I can suggest to use two date tables but again I don't know what is in your model and what measures you have. 

[tamerj1]

Hi @nbs33 

Try make this relationship "Single"

 

("Companies" filters "Append CRM Data") then use CROSSFILTER to convert it to "Both" for the reaquired measures. That might significantly reduce the number of measure that need to be modified. 

[nbs33]

@tamerji I thought I understood how to use CROSSFILTER but my measure doesn't work correctly. The first column of my matrix is company name and if I want to return all Company Types for a customer I used the meausre below. The issue is that it returns a bunch of random values when their is only one value for this cusotmer in the table T2. 

Activity Date Measure = 
    CALCULATE(SELECTEDVALUE('Append CRM Data'[Activity Date]), CROSSFILTER(Companies[id], 'Append CRM Data'[company_id], Both))

[nbs33]

Thank you @tamerj1  for the response. That would definitly work but I would still have to write a measure for almost every column in one of my matrixes. I was wondering if there was a simplier solution.