Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.
Did you ever figure out how to do this?
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
User | Count |
---|---|
70 | |
70 | |
34 | |
23 | |
22 |
User | Count |
---|---|
96 | |
94 | |
50 | |
42 | |
40 |