locked
DAX function for converting text into a function that could be evaluated RRS feed

  • 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 :)

    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

    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/en-us/query-bi/dax/calculate-function-dax?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/related-if-and-calculate-dax-functions-for-the-powerpivot-beginner

    To better solve your question, please type out 5-10 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.com

    Monday, 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

    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