Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
44 | |
20 | |
20 | |
16 | |
15 |
User | Count |
---|---|
46 | |
28 | |
25 | |
18 | |
17 |