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). 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 )
Moda (medida de tendência central, valor que mais se repete) de uma coluna virtual.
Olá, Preciso calcular a moda (medida de tendência central da estatística, valor que mais se repete) de uma coluna virtual de quantidade de clinetes por semana. Minha coluna é virtual, e para calcular qual é o valor que mais se repete dessa coluna preciso fazer uma outra tabela virtual para contar quantas vezes cada valor se repete. mostrar o maior valor. Fiz, porém deu erro. Segue o print. Alguém consegue me ajudar com essa medida?
Prevent filters from impacting a virtual table inside a measure
We have created some measures which appeared to work as seen in the screenshot below. However when adding further fields these measures "break", note the lack of values for # New MQL's Converted to Opportunity and # New MQL's Converted to Closed Won. The DAX for these measures is # New MQL's Converted to Opportunity = VAR __table = SUMMARIZE ( fact_pipeline_events, fact_pipeline_events[pipeline_journey_id], "@has_events", [# New MQL's] > 0 && [# Opportunity Created Events] > 0 ) VAR __filtered_table = FILTER ( __table, [@has_events] = TRUE () ) VAR __journey_id_table = SUMMARIZE ( __filtered_table, fact_pipeline_events[pipeline_journey_id] ) VAR __result = CALCULATE ( [# New MQL's], __journey_id_table ) RETURN __result # New MQL's Converted to Closed Won = VAR __table = SUMMARIZE ( fact_pipeline_events, fact_pipeline_events[pipeline_journey_id], // dim_pipeline_event[stage_name], "@has_events", [# New MQL's] > 0 && [# Stage Name Change Events] > 0 ) VAR __table_filtered = FILTER ( __table, [@has_events] = TRUE () ) VAR __table_with_stage_name = SUMMARIZE ( fact_pipeline_events, fact_pipeline_events[pipeline_journey_id], dim_pipeline_event[stage_name] ) VAR __table_with_stage_name_closed_won = FILTER ( __table_with_stage_name, dim_pipeline_event[stage_name] = "Closed Won" ) VAR __joined_tables = NATURALINNERJOIN ( __table_filtered, __table_with_stage_name_closed_won ) VAR __journey_id_table = SUMMARIZE ( __joined_tables, fact_pipeline_events[pipeline_journey_id] ) VAR __result = CALCULATE ( [# New MQL's], ALL (fact_pipeline_events), __journey_id_table ) RETURN __result So my question is how do we stop the filters that are applied by adding the columns to the visual from impacting the result of the measure?
