Finding value based on row header and a column value

[Anonymous]

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

[Mani1404]

Can you please share the link to Power Bi Sample file. i am doing simalar solution, but stuck in DAX

[tamerj1 · Accepted solution]

HI @Anonymous 
Here is the sample file with the solution based on unpivotted table https://we.tl/t-mPd68FBRV3

Tub Factor = 
MAXX ( 
    FILTER (
        RELATEDTABLE ( 'Material List' ),
        'Material List'[Fleet Size] = 'Daily Production'[Fleet Size]
    ),
    'Material List'[Value]
)

[Anonymous]

Hi, thanks a lot. There seems to be something wrong with the link, it shows it has 0 files. Can you perhapse assist with sharing via another platform? 

[tamerj1]

@Anonymous 
I have updated the link in the original reply. Please check and confirm

[Anonymous]

Works perfectly, thanks very much!

[tamerj1]

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.

[Anonymous]

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

[tamerj1]

@Anonymous 
Better to share PBI sample file. You can share a download link via onedrive, dropbox, weshare, etc..

[Anonymous]

See below, let me know if it works. I tried to keep the sample as bare as possible.

https://www.dropbox.com/sh/9mt2v32y4akfirr/AACBdcq_jhtt9jytxju6prpua?dl=0