DAX LOOKUPVALUE Function Expects a Column Reference as Argument Number 2

Question

I have a star schema model.

I have 2 tables, they look like this.

D_COURSES
Course_ID
Course_Name
Subject_Order

F_STUDENTCOURSES
COURSE_ID
POINTS_CAT_A
POINTS_CAT_B

I want to take the F_STUDENTCOURSES[COURSE_ID] value and look up the Subject_Order value in the D_COURSES table. If that value is 1, I want to sum the POINTS_CAT_A or POINTS_CAT_B value, for a divide function.

This is the DAX funciton I am using:

 divide(
    calculate(sum(F_STUDENTCOURSE[POINTS_CAT_A]),LOOKUPVALUE(D_COURSES[SUBJECT_ORDER],F_STUDENTCOURSE[COURSE_ID],1)),
    calculate(sum(F_STUDENTCOURSE[POINTS_CAT_B]),LOOKUPVALUE(D_COURSES[SUBJECT_ORDER],F_STUDENTCOURSE[COURSE_ID],1)), "") 

The error message I am getting is this:

Function LOOKUPVALUE expects a column reference as argument number 2.

What am I doing wrong?

Answer 1

Lookupvalue works this way lookupvalue(column to return, column to search in, value to search for from the column). In your case it would be lookupvalue(d[subject],d[course_id],f[course_id])....returns the d_subject by lookingup f_course_id values in d_course_id column.

Answer 2

I don't think Lookupvalue works inside calculate. Can you please try the following

MEASURE=
VAR _1 = Calculate(sum([catA]),Filter (values(d[sub_order]), d[sub_order]=1)) 

VAR _2 = Calculate(sum([catB]),Filter (values(d[sub_order]), d[sub_order]=1)) 

VAR _3 = DIVIDE (_1,_2) 

RETURN _3 

Assuming there is relationship it sums up catA and catB when suborder=1 and then divides