Skip to main content

Power BI

New

Save Visual calcs at the Semantic Model (aka Dataset)

Vote (93) Share
Renato Lira's profile image

Renato Lira on 20 Feb 2024 11:51:01

With the amazing release of Visual Calculations (aka Visual calcs) we now have an excel-like way to write metrics on top of the result of a DAX Query.


The problem:

Unfortunately the current implementation took the visual name literally. It has a great interface to help the analyst to apply further business logic by actually seeing (visually) the data, but the result of this business logic isn't stored in the semantic model (the natural place for a business logic to live), it's stored inside the visual at the report level.


Inside the same report, the same metric generated inside a visual calc might exist in several visuals/pages. If you need to change or correct this business logic you should remember to change in every single visual. This situation becomes even worse if you think about golden datasets, with multiple report creators...


The possible solutions I can see:


1) Promote visual calcs to model measures


or


2) Create a new section within TOM, such as a "View" like a SQL View. 



That contains the exact same inputs that we can use in a DAX Visual calculations today:

-Helper base visual to support maintenance of the visual calculations

-Model group by lineages (columns) that determines the granularity

-Model measures (implicit or explicit) that give us numeric input

-DAX Visual calculations expressions (that depends on the structure of rows/columns of the base visual)


To make it more universal, double axis data should internally be unpivoted so the data is stored in a tabular format. This way, any future reference to this "view" can use the groupby columns, implicit/explicit measures previously chosen and the visual calculations.


So, whenever you want to add data to a visual you should choose if you want it from the regular semantic model (old way) or from a specific view(new way).


If you choose a view, every field (unpivoted, tabular version) is available to be used in the visual, so you can use or hide them. 


At Query time, the external-to-the-visual filter context is used to filter and materialize the "view", therefore the outputs should be consistent with the "slice and dice" experience.


This way, whenever the business logic is changed within the view's scope (that is stored at the semantic model), every report that points to it will be automatically updated.


This might solve this maintenance problem, with minimal change of the current implementation, and give other possibilities (like the ability for the visual show a different calculation granularity) for business analysts to actually model with a similar mental model to what they're already doing in Excel.