Forum Discussion
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. Yellow is the data and green is the look up.
Example: Location East is a type A location and has 51 hours it needs to check yellow for the correct type and closest hours then diplay the right template.
Any help would be greatly appreciated.
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
7 Replies
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. Yellow is the data and green is the look up.
Example: Location East is a type A location and has 51 hours it needs to check yellow for the correct type and closest hours then diplay the right template.
Any help would be greatly appreciated.
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
