Build dynamic DAX variable string and return value

[Anonymous]

I want to pick up part of a DAX formula from a dataset, and then return the value of that formula calculation

For eg:

RuleStringCol =


VAR RuleString =  // Building the RuleString variable by string concatenation, using the string in RulesTable.Rule column
"=CALCULATE(COUNTROWS('Dataset1'),
FILTER(ALL('Dataset1')," & RulesTable[Rule] & "))"



RETURN RuleString

But I find that while RuleString is correctly populated with the value from RulesTable.Rule and then concatenated into a CALCULATE expression, this expression does not compute. How do I get it to?

[Cookistador]

any news about this topic?

[v-yulgu-msft]

Hi @Anonymous,

If we wrapped the calculation expression with double quotes ("") in a variable definition, the returned result will be the static string, rather than the result value calculated by the expression. In your scenario, why didn't you directly write the calculation expression? What is the purpose of double quotes? 

For example, a common formula can be:

CumulativeTotal =
VAR var1 =
    CALCULATE (
        COUNTROWS ( Applications ),
        FILTER (
            ALL ( Applications ),
            Applications[Date] <= MAX ( Applications[Date] )
        )
    )
RETURN
    var1

For more advice, please post sample data and show us your expected output.

Best regards,

Yuliana Gu

[Anonymous]

@v-yulgu-msft

The calculation expression is dynamic: it is retrieved from a table in an external data source. So there should be 2 steps:

1. To resolve the expression: this is why I have used the concatenator operator and quotes.

2. To execute the expression. 

Step 1 is happening correctly. I am getting the var RuleString to be populated as:

CALCULATE(COUNTROWS('MyDataset'), FILTER(ALL('MyDataset'),'MyDataset'[Source System] = "MainFrame"))

My issue is that step 2, i.e. to actually execute the CALCULATE statement, is not happening. 

Hope I am explaining the issue more clearly.

[Stachu]

to my knowledge in DAX you cannot execute a code that resides in a string, it will be just considered text and not proper syntax
so even though the string contains valid code the engine interprets it as if it was wrapped in ""

[Anonymous]

If this is the case it is quite a limitation. Dynamic SQL has been around a long time.

[arunmallick]

I agree, it's quite a limitation to such a powerful language. I am too looking for this feature to be available soon. 

In case if you found any alternatives, then please let me know. 

Thank you,

Arun

[Stachu]

true, and I would love to see that feature, but DAX is serving different purpose than SQL, if think comparison with MDX is more in accurate - no clue if dynamic MDX was a thing

[Anonymous]

If you're still looking for a solution to this, you may find value in this post:  https://github.com/TabularEditor/TabularEditor/issues/706