DAX LOOKUPVALUE with IF/OR included?

Evolution

New Member
Joined
Aug 23, 2018
Messages
3
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


 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Formula:
Code:
=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:
r1Xvvwp.jpg
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,044
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top