Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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

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 valu...
Need Help
  • V-lianl-msft's avatar
    V-lianl-msft
    5 years ago

    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.

V-lianl-msft's avatar
V-lianl-msft
Icon for Community Support rankCommunity Support
5 years ago

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.

Related content