Forum Discussion

kb_barge's avatar
kb_barge
Frequent Visitor
7 years ago

The expression refers to multiple columns Multiple columns cannot be converted to scalar value Error

I want to show last year store count based on from date and to date .Getting the following error :The expression refers to multiple columns .Multiple columns cannot be converted to scalar value.
 
 
LastYearStoreCount =
VAR lastFromDate =
SELECTEDVALUE(WUSA_VIEW[From_Date1], TODAY()) - 364
VAR lastToDate = SELECTEDVALUE(WUSA_VIEW[To_Date1], TODAY()) - 364
RETURN
IF([DateDiff] > 365, 0, IF( FILTER(ALL(WUSA_CAL_DIM),
WUSA_CAL_DIM[End_Date] >= lastFromDate && WUSA_CAL_DIM[End_Date] <= lastToDate),DISTINCTCOUNT(WUSA_STORE_DIM[Store Code])
 
))
Need Help

1 Reply

  • tex628's avatar
    tex628
    Community Champion
    7 years ago

     

    LastYearStoreCount =
VAR lastFromDate =
SELECTEDVALUE(WUSA_VIEW[From_Date1], TODAY()) - 364
VAR lastToDate = SELECTEDVALUE(WUSA_VIEW[To_Date1], TODAY()) - 364
RETURN
IF([DateDiff] > 365, 0, 
    IF( FILTER(ALL(WUSA_CAL_DIM), WUSA_CAL_DIM[End_Date] >= lastFromDate && WUSA_CAL_DIM[End_Date] <= lastToDate),DISTINCTCOUNT(WUSA_STORE_DIM[Store Code])
))

    There is something wrong in the final if statement. Your filter statement is covering the entire if so there is no actual comparison happening. 
    I think what you actually need is a calculate statement instead of the if statement:

    CALCULATE(
DISTINCTCOUNT(WUSA_STORE_DIM[Store Code]),
ALL(WUSA_CAL_DIM),
WUSA_CAL_DIM[End_Date] >= lastFromDate,
WUSA_CAL_DIM[End_Date] <= lastToDate
)

     

Related content