Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us at FabCon Atlanta from March 16 - 20, 2026, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM. Register now.

Reply
kb_barge
Frequent Visitor

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])
 
))
1 REPLY 1
tex628
Community Champion
Community Champion

 

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
)

 


Connect on LinkedIn

Helpful resources

Announcements
Fabric Data Days Carousel

Fabric Data Days

Advance your Data & AI career with 50 days of live learning, contests, hands-on challenges, study groups & certifications and more!

October Power BI Update Carousel

Power BI Monthly Update - October 2025

Check out the October 2025 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.