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

DAX DAX Calculations
1

Hello,

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

image
image1321Ă—647 35.7 KB

Final Result Table =
VAR Batch_Number_Result_Table =
CALCULATE (
MAX ( Test_Attribute_Results[Batch Number] ),
Test_Attribute_Results[Batch Number] <> “Blank”
)
VAR LCode =
CALCULATE (
SELECTEDVALUE ( Procurement[Vendor] ),
Test_Attribute_Results[Batch Number] = Batch_Number_Result_Table
)
VAR Vendor =
CALCULATE (
SELECTEDVALUE ( Procurement[Vendor] ),
Test_Attribute_Results[Batch Number] = Batch_Number_Result_Table
)
RETURN
ADDCOLUMNS (
SUMMARIZE (
Test_Attribute_Results,
Test_Attribute_Results[Batch Number],
Test_Attribute_Results[Attribute]
),
“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

2

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

3

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

Thanks
Ankit J

4

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]
image

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

Thanks again for your time!

5

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)])
return 
NATURALINNERJOIN(test1,Proc2)

Thanks
Ankit J

1 Like
6

Awesome!!! Thanks heaps!