Forum Discussion
Solved
2 Criteria look up
New to power BI here but slowly picking it up, I'm trying to complete a two criteria look up like the example below. Two data fields to pick from so it displays the correct template set. Yel...
There might be an easier way to do this but this one worked for me
template = var T = GreenTable[Type] var gh = GreenTable[Hours] var h = CALCULATE(MINX(YellowTable,ABS(YellowTable[Hours]-gh)),YellowTable[Location Type]=t) Var h2 = LOOKUPVALUE(YellowTable[Hours],YellowTable[Hours],gh + h) Var h3 = if(h2 = blank(), LOOKUPVALUE(YellowTable[Hours],YellowTable[Hours],gh - h), h2) Var Template = CALCULATE(max(YellowTable[Template]),YellowTable[Location Type] = t, YellowTable[Hours] = h3) return Template
There might be an easier way to do this but this one worked for me
template = var T = GreenTable[Type] var gh = GreenTable[Hours] var h = CALCULATE(MINX(YellowTable,ABS(YellowTable[Hours]-gh)),YellowTable[Location Type]=t) Var h2 = LOOKUPVALUE(YellowTable[Hours],YellowTable[Hours],gh + h) Var h3 = if(h2 = blank(), LOOKUPVALUE(YellowTable[Hours],YellowTable[Hours],gh - h), h2) Var Template = CALCULATE(max(YellowTable[Template]),YellowTable[Location Type] = t, YellowTable[Hours] = h3) return Template
Thanks for the help, it doesn't seem to qutie work and I think the issue is with the Type field which I've listed as ABC but it is actually 1, 2 or 3 as well as the hours and location being in two different sheets.
Are you able to provide a sample PBIX file
Not to worry, I made a slight tweak and got it working.
Thank you!
Great make sure to mark the thread as solved.