Forum Discussion
Help with LOOKUPVALUE
If the value you're trying to filter on isn't in the column you're using as a filter, there's not really a way around that. It sounds like you're just not using the right column as a filter, but it's hard to keep track of what is what in your description. It would help to have table and column names for everything you're talking about, and a clearer description of what you're using as a filter and how exactly you're using it.
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] ) )
