Forum Discussion
LOOKUPVALUE and Nested IF
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 |
3 Replies
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?
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.
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
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
