LOOKUPVALUE and Nested IF

[ndo03001]

I have a database in DirectQuery where each row has a record of a specific course (COURSE: string) an individual (IND_ID: string) took in a particular period of time (YRTR). The courses have different levels (LVL). I want to mark the individual (IND_ID) as "Entry" if they took even one Entry course (D, D2 or D3) within a specific period of time (YRTR).  The purpose is to have a way of flagging someone as Entry at a point in time which can change from yrtr to yrtr so that I can use that as a base for some of my visuals.

EntryStdnt = IF(LOOKUPVALUE(TABLE1[LVL], TABLE1[YRTR], TABLE1[YRTR], TABLE1[IND_ID], TABLE1[IND_ID]) ="D" || "D2" || "D3", "Entry" ,"Upper")
I pieced together the measure above based on suggestions for similar problems noted on the community board. However when I use this formula I get the following error: A single value for column IND_ID and YRTR in TABLE1 cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifiying an aggregation such as min, max, count, or sum to get a single result.

Suggestions for next steps would be wonderful.

YRTR	IND_ID	COURSE	SUBJ	COU_NBR	MOD_CODE	LVL	REG_CRHR
Sp19	00001631	000621	ESC	1080	09	L	3
Sp19	00001631	001357	PSA	1020	03	L	3
F19	00001631	000252	ENG	0950	12	D	4
F19	00001631	000208	STS	1021	12	L	2
F19	00001631	000253	ENG	1020	12	L	4
F19	00001631	000221	SOC	1080	12	L	3
F19	00001631	000007	QOM	1051		L	3
F18	00001631	000170	PHI	1035		L	3
F18	00001631	000191	PHI	1020		L	4
Sp18	00004524	000183	QOM	1031	12	L	3
Sp18	00004524	000364	SOC	1033	12	L	3
Sp18	00004524	000209	PHI	1020	12	L	4
F18	00004524	001136	MAT	0030		D2	5
F18	00004524	001510	ENG	0090	09	D	4
F18	00004524	001540	ENG	1021		L	4
F18	00004524	000386	PQB	1031		L	3
Sp19	00004524	001196	ENG	0090	09	D	4
Sp19	00004524	000456	PSC	1020		L	4
Sp19	00004524	000689	ENG	1021		L	4

[Stachu]

I took a slightly different approach here, counting the rows with level D,D2,D3 for a given year and id

EntryStdnt =
VAR __yrtr = TABLE1[YRTR]
VAR __ind_id = TABLE1[IND_ID]
VAR __relevant =
    FILTER (
        TABLE1,
        TABLE1[YRTR] = __yrtr
            && TABLE1[IND_ID] = __ind_id
            && ( TABLE1[LVL] = "D"
            || TABLE1[LVL] = "D2"
            || TABLE1[LVL] = "D3" )
    )
VAR __lvl =
    COUNTROWS ( __relevant )
RETURN
    IF ( __lvl > 0, "Entry", "Upper" )

is the result as you would expect it?

[eosborn918]

@StachuJust tried your suggestion (as a new column) and received an error that the function COUNTROWS is not allowed in DirectQuery models. When I try it as a new measure instead, I get the same error as the original poster (A single value...cannot be determined). Still looking for a solution on this.

[Stachu]

this is the version without COUNTROWS

EntryStdnt = 
VAR __yrtr = TABLE1[YRTR]
VAR __ind_id = TABLE1[IND_ID]
VAR __relevant = FILTER(TABLE1,TABLE1[YRTR]=__yrtr&&TABLE1[IND_ID]=__ind_id && (TABLE1[LVL]="D" || TABLE1[LVL]="D2" ||TABLE1[LVL]="D3"))
VAR __lvl = CALCULATE(MIN('TABLE1'[LVL]),__relevant)
RETURN
IF(ISBLANK(__lvl),"Upper","Entry")

but based on this reference

https://docs.microsoft.com/en-us/analysis-services/tabular-models/dax-formula-compatibility-in-direc...

you should now get error because of FILTER, as it's calculated column and not a measure. Can you test and confirm that's the case?

How do you plan to use the new column? if it's in the calculation only we could create a measure for it