Forum Discussion
Finding value based on row header and a column value
Good Day
I want to extract information from another table based on 2 variables. In the below data set I want to determine the "Tub Factor" column so I can multiply it by the loads. Call the below Field "Daily Production"
To get the tub factor I need to refer to the below field "Material Type". So essentially what it must do is in the "Tub Factor" column I want it to first check the "Fleet Size" and match it to the column in the "Material Type" then check the "Application" and match it to the application. In excel I would do a clustered MATCH inside a VLOOKUP. Something like VLOOKUP(application, "material type", match(fleet size, the top row of "material type"),false)
I have tried to un-pivot the material type field in power query to get the below, howev er i then cannot link the databases via application
HI Anonymous
Here is the sample file with the solution based on unpivotted table https://we.tl/t-mPd68FBRV3Tub Factor = MAXX ( FILTER ( RELATEDTABLE ( 'Material List' ), 'Material List'[Fleet Size] = 'Daily Production'[Fleet Size] ), 'Material List'[Value] )
9 Replies
Hi Anonymous
If you you unpivotted the table then you are on the right track. Why aren't you able to create a relationship? Still you can match even without a relationship bu the relationship would be much easier and faster. Still not clear what exactly are you trying to match and which value are you trying to grap from which table to which table. Cannot fund the Material Type column anywhere in the provided screenshots. Also what exactly is the Tub Factor? Is it the value that you are trying to calculate?
lease provide a simplified dummy sample data along with the expected results.Sorry, I mean't Material List, not Material Type. I am unsure how to attach a sameple set, should I attach as an excel or a Power BI?
Just to simplify and elaborate.
I have my main Data "Daily Production" and a table that I need to draw info from "Material List".
I was the tub factor column to first check the Fleet size and match the column, and then return associated value in the row matching the Application. Below in the tub factor column is what should be returned.
Daily Production EIID Application Fleet Size Tub Factor AD4001 MMO AD40 13.95 AD4001 GHA AD40 14.55 AD6001 ZRT AD60 22 AD6002 MMO AD60 18.5 Material List Application AD20 AD25 AD30 AD40 AD50 AD60 RD90 MMO 10,5 13,95 18,5 31 GHA 10,5 14.55 19 31 ZRT 15 22 27 BPO 18 30 Below is the error I get if I unpivot
Anonymous
Better to share PBI sample file. You can share a download link via onedrive, dropbox, weshare, etc..
HI Anonymous
Here is the sample file with the solution based on unpivotted table https://we.tl/t-mPd68FBRV3Tub Factor = MAXX ( FILTER ( RELATEDTABLE ( 'Material List' ), 'Material List'[Fleet Size] = 'Daily Production'[Fleet Size] ), 'Material List'[Value] )Anonymous
I have updated the link in the original reply. Please check and confirm
Can you please share the link to Power Bi Sample file. i am doing simalar solution, but stuck in DAX
