Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
any news about this topic?
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
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.
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 ""
If this is the case it is quite a limitation. Dynamic SQL has been around a long time.
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
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
If you're still looking for a solution to this, you may find value in this post: https://github.com/TabularEditor/TabularEditor/issues/706
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |