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/
Jeroen ter Heerdt (administrator) on 20 Feb 2024 22:23:33
Visual calculations are in preview since February 2024: https://powerbi.microsoft.com/en-us/blog/visual-calculations-preview/.
Update 10/17: This is now in our upcoming roadmap and we will share more details in the coming months.
March 2022 update: We are planning to support this, but we currently do not have a timeline
- Comments (68)
RE: DAX calculations at visual level of reports
A idéia é ótima e levará a programação DAX para um nível superior. Muito mais intuitivo e interessante de aprender.
RE: DAX calculations at visual level of reports
This would make DAX so much easier to learn and use. It can be incredibly frustrating to see what you want to be able to do but not be able to intuitively access the "cell' you need.
I also really like the idea of quick tables, virtual tables to be able to create snapshots at different granularities easily would dramatically improve the ability to analyse the data.
RE: DAX calculations at visual level of reports
Even better if it also provided a simple way to write formulas that work with the data displayed in *another* visual too. Although I suspect that is a dream, as it would require PBI to expose some kind of Visuals.Members list to the user.
RE: DAX calculations at visual level of reports
I would suggest an alternate path would be to
1. Port Excel PivotTables to be available within Power BI Reports.
2. Expand the number of table structure based calculations (such as % of Parent Row Total) that PivotTables can perform
3. Improve the user interface for creating PivotTable OLAP Tools calculated members, measures, and sets.
Why PivotTable technology is a good fit:
PivotTable technology
1. Already works against Tabular Analysis Services.
2. Already has highly-developed user interface patterns.
Currently:
Excel PivotTables provide drag-and-drop/point-and-click computations relative to report table structure against a Tabular Analysis Services source via "Show Values As...". These calculations are performed by the PivotTable engine directly.
Excel PivotTables create an MDX Query behind the scene, which is processed by Power BI's built-in MDX Calculation Engine.
So, the technology is already proven.
Where this path would lead:
More "Show Values As..." options on PivotTables, allowing further easy definitions of calculations based on table structure.
More MDX features supported in a user-friendly way via the PivotTable interface. The current Excel PivotTable uses only a fraction of MDX's power. For example, Report Builder's interface supports side-by-side hierarchy groups, instead of the single hierarchies that PivotTables currently support.
RE: DAX calculations at visual level of reports
Strong support for this idea. Would also love a visual representation of the dependencies between a DAX measure and any underlying base DAX measures that are used.
RE: DAX calculations at visual level of reports
A brilliant idea which can make PowerBI more closer to being a true self service BI tool.
RE: DAX calculations at visual level of reports
Brilliant idea! This would help promote a data culture within organisations by empowering more users and easing the transition for the enormous amount of people familiar with excel to create their own reports in power BI👌
RE: DAX calculations at visual level of reports
Marco - a very interesting idea. And one I would certainly suggest is worth further thought. Thanks for raising!
We have a lot of (let’s call them) traditional excel users who are starting to want to move beyond using PBI just as a tool to read reports in apps produced by others to see if they can migrate their own traditional excel work into PBI. They are dipping their toes into the desktop and playing. Many/most subsequently run back to excel as the experience of data manipulation is too alien. Exactly as you say, the lack of being able to interact directly with the visual table is confusing.
This causes frustration with PBI for those users as well as regret by model/report developers such as myself that I am “losing” them back to the uncontrolled data badlands of Excel where “answers” are unrepeatable and measures calculated I know not how.
A solution that would allow these traditional excel users to feel comfortable in PBI and able to work with models and measures built centrally would go - in my humble opinion - a long way towards both improving the level of data integrity in many businesses (silo reduction) as well as promoting PBI as a business tool by removing a pretty “scary” barrier to entry for most.