Getting a value from two inactive relationships

[Anonymous]

Hello Experts,

The scenario is as below:

Calendar table ---> FactJobActions (Inactive relationship based on CalendarDateKey and CreatedOnDateKey)

FactJobActions ----> FactJobApplications (Inactive relationships based on CandidateJobIdKey)

I want the distinct count  of CandidateJobIdKey from FactApplications where the job actions fall between a specified created on date. Here both the relationships will come into play i.e. calendar ---> FactJobActions to filter only the job actions during that period and FactJobActions ---> FactJobApplications where only the applications related to the filtered job actions would be considered.

I have tried the below calculation but it gives me a wrong result:

CALCULATE( DISTINCTCOUNT(FactJobApplications[CandidateJobIdKey])
, USERELATIONSHIP('Calendar'[CalendarDateKey], FactJobActions[CreatedOnDateKey])
, USERELATIONSHIP(FactJobActions[CandidateJobIdKey], FactJobApplications[CandidateJobIdKey])
)

i think the date filter does not apply in the result that i get using the above calculation.

Hope someone knows the way around this.

Thanks in advance.

Kind regards,

Chetan

[Anonymous]

Just an update to this...the exact same formula works...

In my case the issue was caused becaused of another active One-to-One relationship having filter direction as both by default.

The should an option where One-to-One should have a single direction.

Anyways i worked around this by making that particular One-to-One relationship as inactive and handling the same in DAX.

Anyways thanks all for the time and efforts.

[lbendlin]

It is possible to modify a 1:1 relationship into a 1:M relationship with single search direction.  Double click the connector, then identify which table is more fact-y than the other, and set that one to "Many". Then set the direction to single.

[v-xiaotang]

Hi @Anonymous 

Have you solved this problem? If yes, could you kindly accept the answer helpful as the solution (or kindly share your solution ). so the others can find it more quickly.
really appreciate!
Any question, please let me know. Looking forward to receiving your reply.

Best Regards,
Community Support Team _Tang

[v-xiaotang]

Hi @Anonymous 

have you solved this question?

if not, 

based on this example, you can try to use 

Measure = CALCULATE(
DISTINCTCOUNT(FactJobApplications[CandidateJobIdKey]), 
USERELATIONSHIP(FactJobActions[CandidateJobIdKey], FactJobApplications[CandidateJobIdKey])
)

if question still not solved, could you share your sample file and your expected result? so that we can work on it further.

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin · Accepted solution]

Flip this one around

, USERELATIONSHIP(FactJobActions[CandidateJobIdKey], FactJobApplications[CandidateJobIdKey])

and add the search direction.  Is Bidirectional search really necessary for your scenario?