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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.