Help with LOOKUPVALUE

[elmer1]

Hi - having trouble with a lookup and wondered if anyone here could help. I'm calculating an average and then looking up a corresponding value e.g. Avg = 22, Lookup Table Has 20=Apple, 21=Orange, 22=Banana. so would return Banana

AvGrade = LOOKUPVALUE('Lookup'[Text],'Lookup'[Avg],ROUND('Results'[Avg],0))

Avg = AVERAGE('Results'[PTS])

The trouble I'm having is that when the list of results is filtered in the Results table, the average being calculated is not being found because it's value isn't in the original list:

so filtered list of results = 1, 2, 6, 4, 2 - the average is 3 but the lookup can't find 3 in the filtered list of results so doesn't return.

Hope I've explained clearly enough that someone can help, and apologies my DAX isn't great but I'm thinking I need to use the ALL funcion somehow? 

[KHorseman]

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.

[elmer1]

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?

[greggyb]

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] )
)