Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
alchen00
Helper I
Helper I

Avoid Repeating Virtual Table in Every Measure

I have a dynamic chart that changes its appeareance based on selection in a table.  In the example below the orange curve line is the generated result.  I used a Virtual Table (via Summarize) to speed up the calculation.  I have a lot of measures using this approach, and I am afraid repeating this Virtual Table in every measure will cause slow performance (right now it takes up to 3-5 secs for chart to load).    

alchen00_1-1686295895511.png


I was wondering if I can have this Virtual Table centralized (write it once) and refer to it to speed up performance.  I tried adding the Virtual Table as a new Table rather than inside the measure, but this approach doesn't work because Tables are not updated upon query selection (updates at data load). 

Alternatively, are there other approaches or best practices I should follow to improve performance?

Here is the DAX code:

Cv.RoundV_L = 
VAR MinDate = CALCULATE( MIN(Data[Start]), ALLSELECTED(Data) )
VAR MaxDate = CALCULATE( MIN(Data[End]), ALLSELECTED(Data) )
VAR Ticker = [_Ticker]

//Virtual table to make calculation faster --- This is Key ***
VAR _Mini = SUMMARIZE( FILTER( ALL(Data), Data[Date] >= MinDate && Data[Date] <= MaxDate && Data[Ticker] = Ticker ), Data[Date], "L", MIN(Data[Low]), "_RUN", [_Run]  )

//Given a vertex(h,k), find the quadratic equation of a parabola 
// Y = a(X-h)^2 + k or Y = aX^2 + bX + c
// Solve for a, which is a = (Y-k) / (X-h)^2
// Since the vertex is some time to the left or right, thus below combines 2 half curves to make a nice curve 
VAR Ymin = SUMX( FILTER( _Mini, [_RUN] = 0 ), [L] )     //find the Px_L of the starting point at MinDate or RunCount = 0
VAR Vk = MINX( _Mini,[L])                               //Find the lowest point Px_L
VAR Vh_L = SUMX( FILTER( _Mini, [L] = Vk ), [_RUN] )    //Find the RunCount X of the lowest point 

//Left side of vertex -- Curve A
VAR aLeft = DIVIDE( (Ymin - Vk), (0-Vh_L)^2 )
VAR LCurve = SUMX( FILTER( _Mini, Data[Date] = MIN('Date'[Date]) ), aLeft * ([_RUN] - Vh_L)^2 + Vk ) //Date=Min(Date) to show each date (avoid aggregation) 

//Right side of vertex -- Curve B
VAR Xmax = MAXX( _Mini, [_RUN] )
VAR Ymax = SUMX( FILTER( _Mini, [_RUN] = Xmax ), [L] )
VAR Vh_R = Xmax - Vh_L
VAR Xoffset = Vh_L - Vh_R                                //Curve calc starts at Run = 0, thus shift curve to right
VAR aRight = DIVIDE( (Ymax - Vk), (0-Vh_R)^2 )
VAR RCurve = SUMX( FILTER( _Mini, Data[Date] = MIN('Date'[Date]) ), aRight * ([_RUN]-Xoffset - Vh_R)^2 + Vk )

//Putting it together
VAR RunCnt = SUMX( FILTER( _Mini, Data[Date] = MIN('Date'[Date]) ), [_RUN] )
RETURN
    IF( RunCnt <= Vh_L, LCurve, RCurve )

 

1 ACCEPTED SOLUTION
johnt75
Super User
Super User

I don't think there's a way to avoid having the code in each measure, but you might get a performance boost by using ADDCOLUMNS to add the calculated columns rather than SUMMARIZE. 

ADDCOLUMNS (
    SUMMARIZE (
        FILTER (
            ALL ( Data ),
            Data[Date] >= MinDate
                && Data[Date] <= MaxDate
                && Data[Ticker] = Ticker
        ),
        Data[Date]
    ),
    "L", CALCULATE ( MIN ( Data[Low] ) ),
    "_RUN", [_Run]
)

View solution in original post

2 REPLIES 2
johnt75
Super User
Super User

I don't think there's a way to avoid having the code in each measure, but you might get a performance boost by using ADDCOLUMNS to add the calculated columns rather than SUMMARIZE. 

ADDCOLUMNS (
    SUMMARIZE (
        FILTER (
            ALL ( Data ),
            Data[Date] >= MinDate
                && Data[Date] <= MaxDate
                && Data[Ticker] = Ticker
        ),
        Data[Date]
    ),
    "L", CALCULATE ( MIN ( Data[Low] ) ),
    "_RUN", [_Run]
)

Thank you for your wisdom.  I implemented the solution and got a 40% time reduction (360ms vs 213ms).  On a combined basis hope it make it more efficient.  Thank you again.      

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.