DAX LOOKUPVALUE with IF/OR included?

[Evolution]

Hi Guys (and woman),

I've been wandering on this forum for a bit now but cannot find the answer. I'm a bit of a newby when it comes to power bi/power pivot so I hope you can help me out (I'm using power pivot bytheway).

What I would like to have is the following: I have a look up table with 4 variables (in reality it much more rows) and I want to have a calculated column which gives me the "Name" when all 4 variables are met. I used the DAX function LOOKUPVALUE and it works fine when all the variables are numbers. However here is the catch. If a "costcntr" is blank in the lookupvalue any value will do. So in the case of john it can be 2164, 2264, 2364 etc. the same applies for Eva. I was thinking about an OR/IF function but I'm not sure if this could do the trick and how to implement this into the LOOKUPVALUE. Any sugestions on how I can solve this puzzle would be much appreciated? Thank you in advance.

Lookup table
Name_____Costcntr1|Costcntr2|Costcntr3|Costcntr4
Walter________2_______ 4_______6_______5______
Erik__________1_______ 5_______7_______6______
John _________2_______________ 6_______4______
Lisa__________2________4______ 6_______6______
Eva__________2________1_______8______________


Costcntr1|Costcntr2|Costcntr3|Costcntr4|Wanted Outcome
___2________4________6________6________Lisa
___2________3________6________4________John
___2________1________8________9________Eva
___2________1________8________2________Eva

 

[sadboy309]

Formula:

=INDEX($A$2:$A$6,SUMPRODUCT(((($B$2:$B$6=B10)+($C$2:$C$6=C10)+($D$2:$D$6=D10)+($E$2:$E$6=E10))=MAX(($B$2:$B$6=B10)+($C$2:$C$6=C10)+($D$2:$D$6=D10)+($E$2:$E$6=E10)))*(ROW($A$2:$A$6)-1)))

As picture:

 

[Evolution]

Hi Sadboy,

Thank you for your effort, however my question is not excel formula related . If I was i would use an array to solve this one.

In this case I'm looking for a DAX funtion which ik can use as a calculted column in powerpivot. Although this formula works in excel it doesnot work as a DAX function. Therefor I need a little help from this forum.

kr

Regards.

 

[peter789]

I would be tempted to use Power Query. Load the Lookup Table and the Data table as Queries. Then merge the two, joining on the cost centres (use Ctrl to select the 2nd, 3rd and 4th columns). Expand the table to return the name then load this query into the Data Model. All this can be done from the menu, no coding required!
Peter

 

[Evolution]

Hi Peter,

thank you for your advice, currently I do not have Power Query installed since its my company account I cannot install it myself. I will try to get it installed and will provide with feedback if this does the trick, however this will take some time.

In the mean time is there also a solution on how to implement this into a calculated column in power pivot?

 

[peter789]

I can't help you with the DAX problem but I am sure it is soluble. I might be wrong be wrong but my way of working with the Power BI concept is to use Power Query (Get and Transform) for ETL (Extract, Transform, Load) to deliver data to the Data Model, any calculated columns are loaded at this stage. There should then be no need to use calculated columns via Lookupvalue in the data model, although I am certain wiser minds in this forum will tell me I am completely wrong!
Sorry you are stuck with an legacy version of Excel at work. I have often wondered if Information Technology Director's salaries should be negatively index linked to the date of the oldest version of the software they are inflicting on their users!
Peter