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

[Anonymous]

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

[V-lianl-msft · Accepted solution]

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)

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]

@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.

[Anonymous]

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

==

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]

@amitchandak 

may I ask how to attach pbix  here ?