DAX, lookupvalue

[Anonymous]

Hi all, 

I need help with the following:

I have two tables in Power BI. The first table, "GSS", has a column "Overall Index" that shows the raw index score of employees for each month. I created a measure to calculate the averagex of this column to get the average scores of the employees for multiple months as this is required:

(Note - There are filters on the page that must be kept thus keepfilters is used)

Average of Overall Index = 
AVERAGEX(
	KEEPFILTERS(VALUES('GSS'[Overall Index])),
	CALCULATE(DISTINCT('GSS'[Overall Index]))
)

In my second table "GSS Reference Pt Table", I have two columns:

The purpose of this table is: If the average overall index calculated of the employee is greater than the values in column "Index", they will get the respective points.

(E.g. 0.925 Average Overall Index = 65 points, no rounding off)

To solve this, I had manually hardcoded and created the working measure as shown below (which does not refer to the second table):

Avg GSS Points = (if(GSS[Average of Overall Index] < 0.8, 0, if(GSS[Average of Overall Index]<0.81, 5, if(GSS[Average of Overall Index]<0.82, 10,  if(GSS[Average of Overall Index]<0.83, 15, if(GSS[Average of Overall Index]<0.84, 20, if(GSS[Average of Overall Index]<0.85, 25, if(GSS[Average of Overall Index]<0.86, 30, if(GSS[Average of Overall Index]<0.87, 35,  if(GSS[Average of Overall Index]<0.88, 40, if(GSS[Average of Overall Index]<0.89, 45, if(GSS[Average of Overall Index]<0.90, 50, if(GSS[Average of Overall Index]<0.91, 55, if(GSS[Average of Overall Index]<0.92, 60, if(GSS[Average of Overall Index]<0.93, 65, if(GSS[Average of Overall Index]<0.94, 70, if(GSS[Average of Overall Index]<0.95, 75, if(GSS[Average of Overall Index]<0.96, 80, if(GSS[Average of Overall Index]<0.97, 85, if(GSS[Average of Overall Index]<0.98, 90, if(GSS[Average of Overall Index]<0.99, 95, if(GSS[Average of Overall Index]<1, 100, if(GSS[Average of Overall Index]>1, 100))))))))))))))))))))))

Therefore, my question would be: Is it possible to use a lookupvalue or other related function to obtain the average points instead? Similar to how you would do in an Excel Vlookup. As the values in the second table might change every month and hardcoding is not a good solution moving forward.

Thanks!

[Anonymous]

Sorry, but I encountered an issue for 2 possible scenarios, hoping someone can help:

If the employee has an average overall index < 0.8, he should get 0 points and if the employee has an average overall index > 1, he should get 100 points.

With my current code below, it will not account for the above 2 scenarios:

Weighted Avg GSS Points = (LOOKUPVALUE('GSS Reference Pt Table'[Point],'GSS Reference Pt Table'[Index], rounddown(GSS[Average of Overall Index],2))

Thus, I edited it to the following:

Weighted Avg GSS Points = 0.1 * if(rounddown(GSS[Average of Overall Index], 2) < 0.8, 0, if(rounddown(GSS[Average of Overall Index],2) > 1, 100, (LOOKUPVALUE('GSS Reference Pt Table'[Point],'GSS Reference Pt Table'[Index], rounddown(GSS[Average of Overall Index],2)))))

Therefore, how do I refer the "0.8" and "1.0" from my second table into my code above?

[Greg_Deckler]

@Anonymous The equivalent of VLOOKUP is LOOKUPVALUE or using MAXX(FILTER(...),...)

[Anonymous]

Hi  @Greg_Deckler

Apologies, but yes it works. I had initially tried Lookupvalue but I had a wrong understanding of it and thought that you had to utilise multiple IF and Lookupvalues such as the code below:

if(GSS[Average of Overall Index] < first row value of 'GSS Reference Pt Table'[Index], first row value of 'GSS Reference Pt Table'[Point], if(GSS[Average of Overall Index] < second row value of 'GSS Reference Pt Table'[Index], second row value of 'GSS Reference Pt Table'[Point]))

The working code if anyone is wondering:

Weighted Avg GSS Points = (LOOKUPVALUE('GSS Reference Pt Table'[Point],'GSS Reference Pt Table'[Index], rounddown(GSS[Average of Overall Index],2))