Forum Discussion
DAX statement - "to RETURN, or not to RETURN"
"Have(ing) fun with DAX" and wondering why the RETURN statement is not suitable for my examples in the 'NOT works' section. Thank you in advance.
-- DEFINE can only be used once ie globals
-- EVALUATE VAR RETURN can be used multiple and nested
-- works
EVALUATE
VAR _return = "test" -- create local variable
RETURN
{ _return }DEFINE
VAR _return = "test" -- create global variable
EVALUATE
{ _return }
-- NOT works
DEFINE
VAR _return = "test"
EVALUATE
RETURN
{ _return }DEFINE
VAR _return = "test"
--EVALUATE
RETURN
{ _return }
1st case : if you want to use the local Variable Inside EVALUATE:
EVALUATE VAR _return = "test" RETURN { _return }This works because you are defining a local variable `_return` inside an `EVALUATE` statement and immediately returning it.
In other words, the scope of `_return` is within the `EVALUATE` block, so it isaccessible to the `RETURN` statement.
2nd case : Using global Variable with EVALUATE:
DEFINE VAR _return = "test" EVALUATE { _return }
Here, `_return` is defined globally using `DEFINE` outside of any `EVALUATE` context, making it accessible to any `EVALUATE` statement that follows. You are then using this variable directly inside an `EVALUATE` block, so it works.Coming to the cases where it is failing :
3rd case : DEFINE with EVALUATE and RETURN:
DEFINE VAR _return = "test" EVALUATE RETURN { _return }The problem here is that `EVALUATE` expects a table expression to follow it directly. So, when you insert `RETURN` immediately after it without any table expression, it will fail. Keep in mind that`RETURN` is not a standalone statement in the context of DAX queries but is used within `VAR` to return a value.
4th case : DEFINE and RETURN without EVALUATE:
DEFINE VAR _return = "test" --EVALUATE RETURN { _return }
Here you are trying to use `RETURN` without an enclosing `EVALUATE` statement, which is not how `RETURN` is designed to be used in the context of DAX queries. `RETURN` is used to return the result of a `VAR` within an `EVALUATE` statement or similar contexts, not as a standalone statement.
3 Replies
1st case : if you want to use the local Variable Inside EVALUATE:
EVALUATE VAR _return = "test" RETURN { _return }This works because you are defining a local variable `_return` inside an `EVALUATE` statement and immediately returning it.
In other words, the scope of `_return` is within the `EVALUATE` block, so it isaccessible to the `RETURN` statement.
2nd case : Using global Variable with EVALUATE:
DEFINE VAR _return = "test" EVALUATE { _return }
Here, `_return` is defined globally using `DEFINE` outside of any `EVALUATE` context, making it accessible to any `EVALUATE` statement that follows. You are then using this variable directly inside an `EVALUATE` block, so it works.Coming to the cases where it is failing :
3rd case : DEFINE with EVALUATE and RETURN:
DEFINE VAR _return = "test" EVALUATE RETURN { _return }The problem here is that `EVALUATE` expects a table expression to follow it directly. So, when you insert `RETURN` immediately after it without any table expression, it will fail. Keep in mind that`RETURN` is not a standalone statement in the context of DAX queries but is used within `VAR` to return a value.
4th case : DEFINE and RETURN without EVALUATE:
DEFINE VAR _return = "test" --EVALUATE RETURN { _return }
Here you are trying to use `RETURN` without an enclosing `EVALUATE` statement, which is not how `RETURN` is designed to be used in the context of DAX queries. `RETURN` is used to return the result of a `VAR` within an `EVALUATE` statement or similar contexts, not as a standalone statement.The syntax rules are this
once you declare any variable with VAR, you must do one of 2 things. Either declare one or more additional variables and/or use RETURN to indicate you are done. Even if you weren't using variables, you cannot use EVALUATE inside Power BI Desktop.
Thanking you both for your responses. Imagine writing dax without daxstudio :}
The 'dark ages' of pbi developement (as with all m$ products) would have been frustrating :}
Putting this link in for others who end up here as further help.
Using DEFINE, EVALUATE and ORDER BY in Power BI instead of DAX Studio?
