Forum Discussion
Help with LOOKUPVALUE
I'll try and provide a better description - 2 tables 'Lookup' and 'Students'
'Lookup' has 2 columns [AvgPts] (list of numerical values 1..10) and [AvgGrd] (text A..J)
'Students' has 3 columns [Name], [Class], [Result]
I have a measure in 'Students' to calculate average result AvgPts = AVERAGE('Student'[Result])
I have a second measure AvGrade = LOOKUPVALUE('Lookup'[AvgGrade],'Lookup'[AvPts],ROUND('Student'[AvgPts],0))
within the report is a slicer based on [Class] - without the slicer the lookup works fine but when it is applied it seems that the lookup can't find AvgGrd because the value of AvgPts isn't in the list of [Result] values
e.g. without slicer list of values in [Result] column = 1, 2, 7, 6, 5, 10, 9, 5, 7, 9 AvgPts = 6 lookup returns F
with class slicer applied list of values in [Result] column = 2, 9, 9 AvgPts = 10 lookup returns blank (I think because 10 isn't in the list of [Result] when slicer applied
Does this make any more sense?
1. Don't use the table name when referencing your measure names. Measure names are guaranteed to be unique across the model anyway. It gets very confusing, because typically in DAX if you see 'table'[something], we expect [something] to be a column reference. Measures are scalar values and column references cannot resolve on their own to a scalar value. As with any language, code style carries a lot of implicit meaning and while we don't need to write our code identically, it's useful to be in the same general ballpark as one another. It's a question of imposing a cognitive load just to understand one another, or freeing up that overhead to discuss the issues actually relevant to the discussion. Here's a good DAX style guide.
2. I have no idea why you're trying to implement this in the way you're describing, but you can use your existing lookup function with a small modification and it will ignore the slicer context:
// DAX
// Measure
Better Average =
CALCULATE(
<insert all of your existing measure definition here>
,ALL( 'Students'[Class] )
)