Forum Discussion

ndo03001's avatar
ndo03001
Frequent Visitor
6 years ago

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 differen...
eosborn918's avatar
eosborn918
New Member
6 years ago

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's avatar
Stachu
Icon for Community Champion rankCommunity Champion
6 years ago

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-directquery-mode-ssas-2016?view=asallproducts-allversions

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