Using VALUES with RELATEDTABLE

[macfuller]

I have (simplified) 2 lookup tables (Requisitions and Departments) and a fact table (PurchaseOrders). I need to find who the requisitioners are for each department that created a PO for a particular vendor, with the Requesters field in the Requisitions table.

Requisitions is linked to orders by PO#_Line. Departments is linked via Dept field in each table.

Normally in my pivot table I would drag Requisition[Requester] to the measure and be done. It works for departments. But I'm getting all possible requisitioners for each department... the filters on the PurchaseOrders table aren't applying to the Requisition table. I suspect this is because the Requisition <-> PurchaseOrders relationship is 1:1.

So my next thought is to pull Requisition[Requester] into the PurchaseOrders table into a calculated column. But because the relationship is 1:1 I can't use =RELATED(Requisition[Requester]), I have to use RELATEDTABLE. And there's the question. If I wanted a numeric value I can use SUMX etc. and provide RELATEDTABLE(Requisition) as the table parameter. But since I want the text value of the requester I'm not sure how to tweak the DAX to pass RELATEDTABLE as a parameter in VALUES.

Since the tables are 1:1 related I'm not sure about the performance hit if I were to use PowerQuery to add the Requisitions columns to the PurchaseOrder table - the PO table is millions of rows and 30+ columns already.

Thanks for your suggestions!

 

[Matt Allington]

Filters automatically flow from the lookup tables to the fact table -not the other way. So you can automatically filter requisitions using the departments and vice versa. But there is an easy solution using “black magic”.

Assuming there is only 1 person for each PO, this should work (a measure).

=calculate(if(hasonevalue(departments[requestor Name]),values(departments[requestor name])),PurchaseOrders)

 

[macfuller]

The measure builds properly but when I attempt to put it into my pivot table for <50 purchase orders already filtered Excel churns away for longer than I care to wait before hitting Escape<esc>.

With that kind of performance hit I'd rather find a way to populate a column with the related value - I shouldn't be held up by a 1:1 relationship?!</esc>