Return Measure Results and Columns from another fact table based on row


I have two fact tables, with different measures/info that I would like to combine into one table.
Both fact tables have a column batch number which is the same in both tables, but there are multiple lines per table for each batch.

Is it possible to create a lookup or columns based on the batch numbers, using treatas/summarise/add columns?
PBIX and sample of desired result below. Much appreciated
Results_Test.pbix (549.0 KB)

Sample Desired Result.xlsx (23.3 KB)

Tables to Combine

Final Result Table =
VAR Batch_Number_Result_Table =
MAX ( Test_Attribute_Results[Batch Number] ),
Test_Attribute_Results[Batch Number] <> “Blank”
VAR LCode =
SELECTEDVALUE ( Procurement[Vendor] ),
Test_Attribute_Results[Batch Number] = Batch_Number_Result_Table
VAR Vendor =
SELECTEDVALUE ( Procurement[Vendor] ),
Test_Attribute_Results[Batch Number] = Batch_Number_Result_Table
Test_Attribute_Results[Batch Number],
“Last Test Date”, [Last Test Date],
“Lastest Result”, [Latest Test Results],
“P/F”, [Pass/Fail],
“Cost/KG”, [Cost / KG],
“Weight”, [Weight (KG)],
“Vendor”, Vendor
Thanks for your help

Bumping this post for more visibility from our experts and users.

Hi @Juju - Are you looking to join columns from procurement table only for attribute 1 or for all the attributes.

Ankit J

Hi Ankit,

Thanks for your reply.
To join the columns for all the attributes please. The table would be used to see the results for all attributes by Vendor, Cost etc.

The bit I am having most trouble with is pulling the Cost/KG when there isn’t a cost associated with the batch number. If there is no cost per vendor, I would like to do a lookup based on the Voucher Number, and pull the cost batches that have the same cost, is this possible? for example,

IF [Cost/KG] = 0, " Selected Value ( Procurement Voucher) , … look for the cost/kg that isn’t blank for this voucher and return this value…, [Cost/KG]

Instead of 769-731-21 returning Cost/KG of 0, it would return 100

Thanks again for your time!

Hi @Juju - Check if below works for you.

Table = 

var Test = SUMMARIZE(Test_Attribute_Results,Test_Attribute_Results[Batch Number],Test_Attribute_Results[Attribute],"P/f",[Pass/Fail],"Last Test Date",[Last Test Date],"latest Test results",[Latest Test Results])

var Proc = SUMMARIZE(filter(Procurement,Procurement[Posting Type] = "Financial"
            && Procurement[Transaction Type] = "Purchase Order"),Procurement[Batch Number],Procurement[Vendor],Procurement[Voucher Number],"Cost",[Cost],"weigth(kg)",[Weight (KG)],"cost/kg",[Cost / KG])

var Proc1 = ADDCOLUMNS(Proc,"cost/kg1",if([Cost / KG] = 0,var voucher = Procurement[Voucher Number] return maxx(filter(proc,Procurement[Voucher Number] = voucher && [cost/kg] <> 0),[cost/kg]),[cost/kg])

var Test1 = SELECTCOLUMNS(Test,"Batch Number",Test_Attribute_Results[Batch Number] & "","Attribute",Test_Attribute_Results[Attribute],"p/f1",[P/f],"last",[Last Test Date],"latest",[latest Test results])

var Proc2 = SELECTCOLUMNS(Proc1,"batch number",Procurement[Batch Number] & "","vendor",Procurement[Vendor],"voucher",Procurement[Voucher Number],"cst",[Cost],"costkg1",[cost/kg1],"wgth",[weigth(kg)])

Ankit J

1 Like

Awesome!!! Thanks heaps!