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

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'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