Optimize power bi by only sending queries 1 time

[nphadro07]

I have 4 measures:

Sales CY = CALCULATE(SUM(FACT_Invoice[TRX Net Sales AMT]),DIM_Date[Fiscal Year] = YEAR(TODAY()))

Sales PY = CALCULATE(SUM(FACT_Invoice[TRX Net Sales AMT]),DIM_Date[Fiscal Year] = YEAR(TODAY())-1)

Sales YoY = 

VAR _Sales_CY = [Sales CY]

VAR _Sales_PY = [Sales PY]

RETURN

_Sales_CY - _Sales_PY

Sales YoY % =

VAR _Sales_CY = [Sales CY]

VAR _Sales_PY = [Sales PY]

RETURN

DIVIDE((_Sales_CY - _Sales_PY),_Sales_PY)

Requirement is that I show all 4 measures in a table visual, each have to be in their own column of a visual to allow sort by all 4 columns. When I try to optimize the Dax using Dax studio I find that the YoY and YoY % measures are taking the longest naturally. All I need to do is sent two simple queries one time to the engine. Sales CY and Sales PY. From there I wish I could just use the cache to run by Sales YoY and Sales YoY %. Instead I am sending 4 queries and having to calculate Sales CY and Sales PY multiple times and don’t want to do this. I was really hoping visual calcs would solve this issue where it would run the other queries then super quickly do simple thing slike subtracting. Essentially I want to only send queries necessary. Then for YoY or YoY % I want to simply just use the already run queries. I have so many use cases for this. I want to only query a measure once but then use it in multiple columns in different ways. Any thoughts?

[lbendlin]

Yes, Visual Calculations can help you to reduce the compute effort.  What have you tried and where are you stuck?

[nphadro07]

Thanks @lbendlin for your response. Here is what I am stuck on. I have a model that is relatively large (8GB) and I have optimized it significantly and am following all the many many best practices. Below at the bottom of this post you can see a screen shot of two tables that are nearly the exact same. The Measures labeled "Sales INV Year CY" and "Sales INV Year PY" are the exact same in both tables. The dimensions are also the exact same. The only difference is the last column labeled "Sales INV Year YoY. The top table has a typical standard measure from the model.

Sales INV Year YoY = 

VAR _Sales_CY = [Sales INV Year CY]

VAR _Sales_PY = [Sales INV Year PY]

RETURN

_Sales_CY - _Sales_PY

The bottom table in the image below has the exact same results but it is a visual calc:

The difference in performance is CRAZY. The query that the visual calc runs is also crazy complex. What I was hoping for is that visual calcs performance would be better but it is 3x as bad. The top table take 3.4 seconds. the bottom table taks 9 seconds. I was hoping that the way visual calcs work is you could some how say.... let the query run for the first two columns then just subtract the two. I welcome any thougts 🙂