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

but can't found any error in Dax

Any input will helpful

 

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.

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      amitchandak 

      may I ask how to attach pbix  here ?

       

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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

  • V-lianl-msft's avatar
    V-lianl-msft
    Community 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