Answered by:
DAX function for converting text into a function that could be evaluated
Question

Hello all,
My problem is the following.
I am using PowerPivot to build a small reporting DB. I have an excel column (located in the table Rules[Text_Rule] ) which contains DAX formulas in text format for example (easy here for the purpose of illustration and test):
IF(1=1; 0; 1)
Then I am retrieving this cell in a calculated field from another linked table using the Related function, namely:
=RELATED(Rules[Text_Rule])
So far so good, I retrieve my text, in the column (sorry for the lack of image I have a new acocunt :) )
CalculatedColumn1
_______________
IF(1=1;0;1)
But now I want to evaluate this function.So in another column I typed:
=CALCULATE([CalculatedColumn1])
But this lead to an error:
CalculaedColumn2
_______________
#ERROR
So I was thinking that CALCULATE was the DAX equivalent of the excel function EVALUATE. But this seems not to be the case. Do you know if such a function exist? For me, the source of the problem is that my cell is evaluated as a text and not as a function (is there a function type in DAX)?
Also, I tried several option: nesting the CALCULATE and RELATED function; does not work. Typing directly the content of the cell in the CALCULATE function like CALCULATE(IF(1=1; 0) works.
Any ideas? (Using exclusively DAX and Power Pivot, because in Excel and VB I already have the solution)
Thank you :)
 Moved by Terry Xu  MSFT Monday, May 28, 2018 6:44 AM
Friday, May 25, 2018 9:07 AM
Answers

So I was thinking that CALCULATE was the DAX equivalent of the excel function EVALUATE. But this seems not to be the case. Do you know if such a function exist? For me, the source of the problem is that my cell is evaluated as a text and not as a function (is there a function type in DAX)?
The answer to your question is that CALCULATE is not the same as Excel's EVALUATE function and DAX does not have an equivalent function that can take a string argument and evaluate it as a DAX expression. You'll need to alter your design as DAX does not provide a way of passing in strings as dynamic expressions in the way you are trying to do it.
http://darren.gosbell.com  please mark correct answers
 Proposed as answer by willson yuanMicrosoft contingent staff Tuesday, May 29, 2018 1:18 AM
 Marked as answer by ArmBer Tuesday, May 29, 2018 7:22 AM
Tuesday, May 29, 2018 12:52 AM
All replies

Hello ArmBer,
This forum(Excel for Developers) is for developing issues related to Excel Object Model and I think your issue is more related to using DAX in Power Pivot. So I will move the thread to Power Pivot forum.
Thanks for understanding,
Best Regards,
Terry
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.Monday, May 28, 2018 6:43 AM 
Hi ArmBer,
Thanks for your question.
>>>>So I was thinking that CALCULATE was the DAX equivalent of the excel function EVALUATE. But this seems not to be the case. Do you know if such a function exist? For me, the source of the problem is that my cell is evaluated as a text and not as a function (is there a function type in DAX)?
Calculate function do exist. Please refer to below document:
https://msdn.microsoft.com/enus/querybi/dax/calculatefunctiondax?f=255&MSPPError=2147217396>>>Also, I tried several option: nesting the CALCULATE and RELATED function; does not work. Typing directly the content of the cell in the CALCULATE function like CALCULATE(IF(1=1; 0) works.
For IF(1=1; 0), as 1 will always equal 1, which means the condition will always True, thus it will always return 0 for the if statement.
For CALCULATE and RELATED function, please refer to below blog:
http://www.decisivedata.net/blog/relatedifandcalculatedaxfunctionsforthepowerpivotbeginnerTo better solve your question, please type out 510 rows of example data for these table, then showing what results you are expecting based on those sample data? It is much better if you can share the EXCEL work book. Do mask sensitive data before uploading.
Best Regards
Willson Yuan
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.comMonday, May 28, 2018 7:46 AM 
So I was thinking that CALCULATE was the DAX equivalent of the excel function EVALUATE. But this seems not to be the case. Do you know if such a function exist? For me, the source of the problem is that my cell is evaluated as a text and not as a function (is there a function type in DAX)?
The answer to your question is that CALCULATE is not the same as Excel's EVALUATE function and DAX does not have an equivalent function that can take a string argument and evaluate it as a DAX expression. You'll need to alter your design as DAX does not provide a way of passing in strings as dynamic expressions in the way you are trying to do it.
http://darren.gosbell.com  please mark correct answers
 Proposed as answer by willson yuanMicrosoft contingent staff Tuesday, May 29, 2018 1:18 AM
 Marked as answer by ArmBer Tuesday, May 29, 2018 7:22 AM
Tuesday, May 29, 2018 12:52 AM 
Thank you for the reply,
I will write directly the agruments by using a long SWITCH function. Might be not as flexible but it will provide a robust solution.
Tuesday, May 29, 2018 7:23 AM