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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

error : The expression refers to multiple columns. Multiple columns cannot be converted to a scalar

Delion_0-1613463334259.png

Hi

I want to achieve slicer for:

> last day

> last 30 day

> MTD

but I received this error : The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

but can't found any error in Dax

Any input will helpful

 

1 ACCEPTED SOLUTION
V-lianl-msft
Community Support
Community Support

Hi @Anonymous ,

 

Try to create a new table like below:

Table 2 = 
var LastDay = TODAY()-1
var Last30D = TODAY()-30
var MTD_START = DATE(YEAR(TODAY()),1,1)
var t1 = ADDCOLUMNS(FILTER('Table','Table'[Date]=LastDay),"selection","LastDay")
var t2 = ADDCOLUMNS(FILTER('Table','Table'[Date]>=Last30D&&'Table'[Date]<=TODAY()),"selection","Last30D")
var t3 = ADDCOLUMNS(FILTER('Table','Table'[Date]>=MTD_START&&'Table'[Date]<=TODAY()),"selection","MTD")
return UNION(t1,t2,t3)

V-lianl-msft_0-1613726464802.png

Another way is to create measures for different periods of calculation, and then use slicers to display measures for different periods.https://www.fourmoo.com/2017/11/21/power-bi-using-a-slicer-to-show-different-measures/ 

 

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
V-lianl-msft
Community Support
Community Support

Hi @Anonymous ,

 

Try to create a new table like below:

Table 2 = 
var LastDay = TODAY()-1
var Last30D = TODAY()-30
var MTD_START = DATE(YEAR(TODAY()),1,1)
var t1 = ADDCOLUMNS(FILTER('Table','Table'[Date]=LastDay),"selection","LastDay")
var t2 = ADDCOLUMNS(FILTER('Table','Table'[Date]>=Last30D&&'Table'[Date]<=TODAY()),"selection","Last30D")
var t3 = ADDCOLUMNS(FILTER('Table','Table'[Date]>=MTD_START&&'Table'[Date]<=TODAY()),"selection","MTD")
return UNION(t1,t2,t3)

V-lianl-msft_0-1613726464802.png

Another way is to create measures for different periods of calculation, and then use slicers to display measures for different periods.https://www.fourmoo.com/2017/11/21/power-bi-using-a-slicer-to-show-different-measures/ 

 

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

amitchandak
Super User
Super User

@Anonymous , I doubt something is wrong at lastday and monthstart var

 

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
Anonymous
Not applicable

hi @amitchandak 

 

This is DAX I use

 

LastDay/Last30D/MTD Selection =
Var TodayDate = TODAY()
Var YearStart = CALCULATE(STARTOFYEAR(CL_Prod[Month Year]), YEAR(CL_Prod[Month Year]) = YEAR(TodayDate))
Var MonthStart = CALCULATE(STARTOFYEAR(CL_Prod[Month Year]), YEAR(CL_Prod[Month Year]) = YEAR(TodayDate), MONTH(CL_Prod[Month Year]) = MONTH(TodayDate))
Var Result =
UNION (
ADDCOLUMNS (
CALENDAR ( YearStart, TodayDate),
"Selection", "YTD"
),
ADDCOLUMNS (
CALENDAR ( MonthStart, TodayDate),
"Selection", "MTD"
)
)
RETURN
Result

 

 

==

Delion_0-1613492656896.png

 

This is what I want to achieve : create category MTD, YTD beside date

 

I'm following this tutorial

https://www.youtube.com/watch?v=AdLDYohLeJc

Anonymous
Not applicable

@amitchandak 

may I ask how to attach pbix  here ?

 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

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