Measure that references a second table problem

[jcawley]

Good morning Experts,

I'm having a relationship problem or just do not know how to go about this. Likely both.

So I am trying to make a measure that uses a different measure coming from the Sales table, that can references a Status column in my Appointments table.

I could easily make this by adding a LOOKUPVALUE helper column in either my Sales or LookUpTransactionNumber table. BUT! My superior doesn't want to add anymore helper columns to the model if it can be avoided. My relationships are:



The measure I made looks like this:

Net Units from Certain Status =

CALCULATE([Measure that only uses Sales Table],'Appointments'[Status]="A" || 'Appointments'[Status]="B" || 'Appointments'[Status]="C",USERELATIONSHIP('Appointments'[appt_id],LookupTransactionNos[appt_id]))

Now here is my problem. Invoking this USERELATIONSHIP sorta works. But what happens is now the Appointments table is driving the measure. Appointment Date is used instead of the Sale table Date. The Staff member working is now tied to the Appointment table instead of the Sales table. 

What I want to happen is the Sales table still drives the measure - I just want to know if a related status on the Appointment table meets a certain criteria. 

Can this be done without creating a new helper column?

Any help would be greatly appreciated!

[v-zhangti]

Hi, @jcawley 

To calculate the designed table, you need to provide some sample data for testing. Sensitive information can be removed in advance. What kind of expected results do you expect? You can also show it with pictures. I look forward to your response.

Best Regards,

Community Support Team _Charlotte

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