Forum Discussion

ilcaa72's avatar
ilcaa72
Helper IV
8 years ago
Solved

return multiple Vars in Dax

im new to Dax.  here is my Dax formula. 

 

I keep messing up and pressing alt enter, then enter incorrectly which deletes my columns... what is the proper syntax to return all 3 variables at the same time as 3 seperate columns?  thanks

 

dates = 

var basetable =
    CALENDAR(DATE(2016,1,1),today())

var addyears = 
     ADDCOLUMNS(basetable,"year",YEAR([Date]))

var addmonths =
    ADDCOLUMNS(
    basetable,
        "monthid", FORMAT( [Date], "yyyymm"), 
        "month", FORMAT( [Date], "mmm yy")
   )
return ???  (all 4 columns)

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    Try this.  It's a little advanced, and came from www.sqlbi.com  Check out this article for the full explanation

     

    Calendar = 
VAR BaseCalendar =
    CALENDAR ( DATE ( 2016, 1, 1 ), DATE ( YEAR( TODAY() ), 12, 31 ) )
RETURN
    GENERATE (
        BaseCalendar,
        VAR BaseDate = [Date]
        VAR YearDate = YEAR ( BaseDate )
        VAR MonthNumber = MONTH ( BaseDate )
        VAR MonthName = FORMAT ( BaseDate, "mmmm" )
        VAR YearMonthName = FORMAT ( BaseDate, "mmm yy" )
        VAR YearMonthNumber = YearDate * 12 + MonthNumber – 1
        RETURN ROW (
            "Day", BaseDate,
            "Year", YearDate,
            "Month Number", MonthNumber,
            "Month", MonthName,
            "Year Month Number", YearMonthNumber,
            "Year Month", YearMonthName
        )
    )

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Try this.  It's a little advanced, and came from www.sqlbi.com  Check out this article for the full explanation

     

    Calendar = 
VAR BaseCalendar =
    CALENDAR ( DATE ( 2016, 1, 1 ), DATE ( YEAR( TODAY() ), 12, 31 ) )
RETURN
    GENERATE (
        BaseCalendar,
        VAR BaseDate = [Date]
        VAR YearDate = YEAR ( BaseDate )
        VAR MonthNumber = MONTH ( BaseDate )
        VAR MonthName = FORMAT ( BaseDate, "mmmm" )
        VAR YearMonthName = FORMAT ( BaseDate, "mmm yy" )
        VAR YearMonthNumber = YearDate * 12 + MonthNumber – 1
        RETURN ROW (
            "Day", BaseDate,
            "Year", YearDate,
            "Month Number", MonthNumber,
            "Month", MonthName,
            "Year Month Number", YearMonthNumber,
            "Year Month", YearMonthName
        )
    )
  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    8 years ago

    Hi ilcaa72,

     

    Is your code here just a demo? Just see from the code, we don't need to use this complex code. Anonymous's solution is great. Besides, the three variables will return three table type values rather than three columns. If you post the desired result, maybe we can find out a better solution.

     

     

    Best Regards,

    Dale

    • tsakoc's avatar
      tsakoc
      Frequent Visitor
      7 years ago

      Hi 

      Please help me I'm trying to return multplie values into one table column. My code is 

       

      OTPMeasure = var PercDiffEarlyOTP = CALCULATE(DIVIDE([Early OTP Selected Day],[Total OTP Selected Day],1) * 100 - (DIVIDE([Early OTP Past 30 Days],[Total OTP Past 30 Days],1)) *100)


      var PercDiffLateOTP = CALCULATE(DIVIDE([Late OTP Selected Day],[Total OTP Selected Day],1) * 100 - (DIVIDE([Late OTP Past 30 Days],[Total OTP Past 30 Days],1)) *100)


      var PercDiffOnTimeOTP = CALCULATE(DIVIDE([On Time OTP Selected Day],[Total OTP Selected Day],1) * 100 - (DIVIDE([On Time OTP Past 30 Days],[Total OTP Past 30 Days],1)) *100) 

      return PercDiffEarlyOTP ,PercDiffLateOTP ,PercDiffOnTimeOTP