Marco Russo on 22 May 2020 15:48:20
Provide a simple way to write formulas that work with the data displayed in a visual. For example, the running total of a measure in a table or in a chart according to the sort order of the visual should be something like this:
Running Total = ReportData[Sales] + PREVIOUS ( ReportData[Running Total] )
Where ReportData is the content of the current visual, Sales and Running Total are the names of the columns in the current visual. The user is not expected to know the model content, the calculation can be executed on top of any data currently being displayed.
More details about a proposal for visual calculations in DAX here:
https://www.sqlbi.com/articles/a-proposal-for-visual-calculations-in-dax/
- Comments (56)
RE: DAX calculations at visual level of reports
I really like this idea, this will give better separation of the reporting model measures versus the semantic model calculations or measure
RE: DAX calculations at visual level of reports
Yes, indeed. XLCubed is a good alternative. It allows the creation of calculations at the visual (table) level.
RE: DAX calculations at visual level of reports
As an Accountant/Finance type, I’m finding it difficult to convince others in my field to invest what they would need to in DAX. Calculations at a visual level would bring this group functionality without having to make the full investment in DAX upfront. My hope would be that this would bring better adoption from a broader business group.
RE: DAX calculations at visual level of reports
Or ... use a client like XLCubed which embraces Excel. No need to wait for future enhancements. Available today!
RE: DAX calculations at visual level of reports
Perhaps a more DAXy step in a similar direction would be to add ordered iterators. Think of SQL's LAG operator but inside a DAX iterator function. While adding the concept of ordering is likely non-trivial under the hood, it would definitely help with Running Total, as well as any other path dependent calculations. I've created an idea with another use case and a possible syntax at
https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/40647124-ordered-iterators
RE: DAX calculations at visual level of reports
When thinking about evaluating competing BI tools MS would do well to note that some very old BI tools have this equivalent functionality; lack of it is creating resistance to PBI adoption at my workplace. DAX is great but not easy as Marco & Alberto would say.
RE: DAX calculations at visual level of reports
Agreed - these "visual functions" would make it easier for new users including advanced Excelers to adopt and see the value of migrating onto a centralised data platform. Otherwise many will simply give up on the DAX mind-twist (context transitioning multi-layer, multi-functions for subtotals!), focus on their work and turn to another tool, avoiding it in future or full roll-out.
Thank you Marco & Alberto for applying your deep understanding to this. Microsoft – you should take up this valuable insight and idea!
RE: DAX calculations at visual level of reports
Another thing that can be possible is to reuse some part of data that we already query (i.e. Highlight the Total Sales of a table/matrix in a Card)
RE: DAX calculations at visual level of reports
I remember how frustrating was when I discover that EARLIER function isn't equivalent to LAG on SQL. With this proposal this can become reality!
RE: DAX calculations at visual level of reports
Very much like this idea!