DAX to Get Sales From Related Table - Relationship Not Found

[snayff]

The Problem

I am trying to create a calculated column that tells me how much has been sold for each row (i.e. each ID) in the `dimBroker` table where:

1. The sale took place in the last year

2. The Broker Status is SP

3. The sale has completed

I am then looking to check if the amount sold is greater than the target. 

Background

`dimBroker[ID]` has a 1-Many relationship with `factLoanData[BrokerID]` .

`dimStatus[ID]` has a 1-Many relationship with `factLoanData[StatusID]`.

Current Progress

Even though there is a direct link I am getting the error stating there is no relationship. Perhaps I am missing something obvious! 

Any help would be appreciated

[snayff]

@johnt75 that did it, thank you very much!

[ToddChitt]

Sounds like you are over-thinking it.

All those requirements you state for the Sales are dimensions of the Sale. Forget about them for getting started. 

Sales = SUM ( factLoanData[AdjustedGrossLoan] )

And where are you putting this measure? If in the Loan Data table, fine. That table can be 'sliced' by your dimension tables of Date, Broker, and Status.

Next, do you have a Goal table that is also joined to those three dimensions? If so, you can have this:

Goal = SUM ( factLoanGoal[LoanGoal] )

Now create a measure for comparing the two:

Meets Goal = IF ( [Sales] > [Goal], "True", False" )

Take the dimensionality out of it. Start with the above. Once you have that, start slicing by varioius dimensions and see what happens.

[johnt75 · Accepted solution]

You need to move the RELATED(dimBroker[ID]) into a variable. There is no row context inside CALCULATE.