cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ilcaa72 Member
Member

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)

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
ChrisHaas Established Member
Established Member

Re: return multiple Vars in Dax

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 3
ChrisHaas Established Member
Established Member

Re: return multiple Vars in Dax

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
        )
    )
Community Support Team
Community Support Team

Re: return multiple Vars in Dax

Hi @ilcaa72,

 

Is your code here just a demo? Just see from the code, we don't need to use this complex code. @ChrisHaas'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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
tsakoc Visitor
Visitor

Re: return multiple Vars in Dax

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