Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Buenos dias, tengo esta formula que funciona perfectamente, pero tarda mucho en cargar cada que que aplico un filtro. Unos 20 segundos de media. Mi pregunta es si se puede optimizar de tal forma que no tarde tanto la carga, soy bastante nuevo y se que habría infinitos caminos pero no se me ocurre nada mas. Muchas gracias de antemano.
Solved! Go to Solution.
@Syndicate_Admin También puedes probar esto:
RANKING POINTS FAST =
VAR _caseOnline =
CALCULATE (
RANKX ( ALL ( 'AGENTS AND SCHEDULES'[AGENT] ), [POINTS],, DESC, SKIP ),
FILTER (
'WEEKLY INCENTIVES',
'WEEKLY INCENTIVES'[SHIFT ONL/RFZ/TLV] = "ONLINE"
)
)
VAR _caseREINFORCEMENT =
CALCULATE (
RANKX ( ALL ( 'AGENTS AND SCHEDULES'[AGENT] ), [POINTS],, DESC, SKIP ),
FILTER (
'WEEKLY INCENTIVES',
'WEEKLY INCENTIVES'[SHIFT ONL/RFZ/TLV] = "REINFORCEMENT"
)
)
VAR _caseTELESHOPPING =
CALCULATE (
RANKX ( ALL ( 'AGENTS AND SCHEDULES'[AGENT] ), [POINTS],, DESC, SKIP ),
FILTER (
'WEEKLY INCENTIVES',
'WEEKLY INCENTIVES'[SHIFT ONL/RFZ/TLV] = "TELESHOPPING"
)
)
RETURN
IF (
OR (
ISFILTERED ( 'TABLE OF DATES'[Nº WEEK AND MONTH] ),
ISFILTERED ( 'TABLE OF DATES'[DAY MONTH] )
),
IF (
AND (
OR (
_caseReinforcement = 1
&& [SALES ONLY BA] >= MAX ( 'WEEKLY INCENTIVES'[BA. A NECESSARY] ),
OR (
_caseREINFORCEMENT = 1
&& [SALES ONLY BA] >= MAX ( 'WEEKLY INCENTIVES'[BA. A NECESSARY] ),
_caseTeleshopping = 1
)
),
[SALES ONLY BA] >= MAX ( 'WEEKLY INCENTIVES'[BA. A NECESSARY] )
),
" :heavy_dollar_sign: Cobra, has " & ROUND ( [POINTS], 1 ) & " points and superavit of "
& [SALES ONLY BA] - MAX ( 'WEEKLY INCENTIVES'[BA. A NECESSARY] ) & "sales",
IF (
AND (
[SALES ONLY BA] >= MAX ( 'WEEKLY INCENTIVES'[BA. A NECESSARY] ),
[SALES ONLY BA] > 0
),
":cross_mark:No cobra, has " & ROUND ( [POINTS], 1 ) & " points and superavit of "
& [SALES ONLY BA] - MAX ( 'WEEKLY INCENTIVES'[BA. A NECESSARY] ) & "sales",
":cross_mark:No cobra, has " & ROUND ( [POINTS], 1 ) & " points and missing "
& ABS ( MAX ( 'WEEKLY INCENTIVES'[BA. A NECESSARY] ) - [SALES ONLY BA] ) & "sales"
)
),
BLANK ()
)
El uso de variables debería acelerarlo
RANKING POINTS FAST =
IF (
ISFILTERED ( 'TABLE OF DATES'[Nº WEEK AND MONTH] )
|| ISFILTERED ( 'TABLE OF DATES'[DAY MONTH] ),
VAR SalesOnlyBA = [SALES ONLY BA]
VAR MaxBANecessary =
MAX ( 'WEEKLY INCENTIVES'[BA. A NECESSARY] )
RETURN
IF (
CALCULATE (
RANKX ( ALL ( 'AGENTS AND SCHEDULES'[AGENT] ), [POINTS],, DESC, SKIP ),
'WEEKLY INCENTIVES'[SHIFT ONL/RFZ/TLV] = "ONLINE"
) = 1
&& SalesOnlyBA >= MaxBANecessary
|| CALCULATE (
RANKX ( ALL ( 'AGENTS AND SCHEDULES'[AGENT] ), [POINTS],, DESC, SKIP ),
'WEEKLY INCENTIVES'[SHIFT ONL/RFZ/TLV] = "REINFORCEMENT"
) = 1
&& SalesOnlyBA >= MaxBANecessary
|| CALCULATE (
RANKX ( ALL ( 'AGENTS AND SCHEDULES'[AGENT] ), [POINTS],, DESC, SKIP ),
'WEEKLY INCENTIVES'[SHIFT ONL/RFZ/TLV] = "TELESHOPPING"
) = 1
&& SalesOnlyBA >= MaxBANecessary,
" :heavy_dollar_sign: Cobra, has " & ROUND ( [POINTS], 1 ) & " points and superavit of " & SalesOnlyBA - MaxBANecessary & "sales",
IF (
SalesOnlyBA >= MaxBANecessary
&& SalesOnlyBA > 0,
":cross_mark:No cobra, has " & ROUND ( [POINTS], 1 ) & " points and superavit of " & SalesOnlyBA - MaxBANecessary & "sales",
":cross_mark:No cobra, has " & ROUND ( [POINTS], 1 ) & " points and missing "
& ABS ( MaxBANecessary - SalesOnlyBA ) & "sales"
)
),
BLANK ()
)
Esta formula ha mejorado solo un segundo la original pero algo es algo, se agradece el esfuerzo.
Puedes probar el siguiente código :-
RANKING POINTS FAST =
IF (
OR (
ISFILTERED ( 'TABLE OF DATES'[Nº WEEK AND MONTH] ),
ISFILTERED ( 'TABLE OF DATES'[DAY MONTH] )
),
IF (
CALCULATE (
RANKX ( ALL ( 'AGENTS AND SCHEDULES'[AGENT] ), [POINTS],, DESC, SKIP ),
FILTER (
'WEEKLY INCENTIVES',
'WEEKLY INCENTIVES'[SHIFT ONL/RFZ/TLV] = "ONLINE"
)
) = 1
&& [SALES ONLY BA] >= MAX ( 'WEEKLY INCENTIVES'[BA. A NECESSARY] )
|| CALCULATE (
RANKX ( ALL ( 'AGENTS AND SCHEDULES'[AGENT] ), [POINTS],, DESC, SKIP ),
FILTER (
'WEEKLY INCENTIVES',
'WEEKLY INCENTIVES'[SHIFT ONL/RFZ/TLV] = "REINFORCEMENT"
)
) = 1
&& [SALES ONLY BA] >= MAX ( 'WEEKLY INCENTIVES'[BA. A NECESSARY] )
|| CALCULATE (
RANKX ( ALL ( 'AGENTS AND SCHEDULES'[AGENT] ), [POINTS],, DESC, SKIP ),
FILTER (
'WEEKLY INCENTIVES',
'WEEKLY INCENTIVES'[SHIFT ONL/RFZ/TLV] = "TELESHOPPING"
)
) = 1
&& [SALES ONLY BA] >= MAX ( 'WEEKLY INCENTIVES'[BA. A NECESSARY] ),
" :heavy_dollar_sign: Cobra, has " & ROUND ( [POINTS], 1 ) & " points and superavit of "
& [SALES ONLY BA] - MAX ( 'WEEKLY INCENTIVES'[BA. A NECESSARY] ) & "sales",
IF (
[SALES ONLY BA] >= MAX ( 'WEEKLY INCENTIVES'[BA. A NECESSARY] )
&& [SALES ONLY BA] > 0,
":cross_mark:No cobra, has " & ROUND ( [POINTS], 1 ) & " points and superavit of "
& [SALES ONLY BA] - MAX ( 'WEEKLY INCENTIVES'[BA. A NECESSARY] ) & "sales",
":cross_mark:No cobra, has " & ROUND ( [POINTS], 1 ) & " points and missing "
& ABS ( MAX ( 'WEEKLY INCENTIVES'[BA. A NECESSARY] ) - [SALES ONLY BA] ) & "sales"
)
),
BLANK ()
)
y también puede intentar mantener todo el código de cálculo en las variables separadas y usarlo en la condición Si.
Gracias
Samarth
@Syndicate_Admin También puedes probar esto:
RANKING POINTS FAST =
VAR _caseOnline =
CALCULATE (
RANKX ( ALL ( 'AGENTS AND SCHEDULES'[AGENT] ), [POINTS],, DESC, SKIP ),
FILTER (
'WEEKLY INCENTIVES',
'WEEKLY INCENTIVES'[SHIFT ONL/RFZ/TLV] = "ONLINE"
)
)
VAR _caseREINFORCEMENT =
CALCULATE (
RANKX ( ALL ( 'AGENTS AND SCHEDULES'[AGENT] ), [POINTS],, DESC, SKIP ),
FILTER (
'WEEKLY INCENTIVES',
'WEEKLY INCENTIVES'[SHIFT ONL/RFZ/TLV] = "REINFORCEMENT"
)
)
VAR _caseTELESHOPPING =
CALCULATE (
RANKX ( ALL ( 'AGENTS AND SCHEDULES'[AGENT] ), [POINTS],, DESC, SKIP ),
FILTER (
'WEEKLY INCENTIVES',
'WEEKLY INCENTIVES'[SHIFT ONL/RFZ/TLV] = "TELESHOPPING"
)
)
RETURN
IF (
OR (
ISFILTERED ( 'TABLE OF DATES'[Nº WEEK AND MONTH] ),
ISFILTERED ( 'TABLE OF DATES'[DAY MONTH] )
),
IF (
AND (
OR (
_caseReinforcement = 1
&& [SALES ONLY BA] >= MAX ( 'WEEKLY INCENTIVES'[BA. A NECESSARY] ),
OR (
_caseREINFORCEMENT = 1
&& [SALES ONLY BA] >= MAX ( 'WEEKLY INCENTIVES'[BA. A NECESSARY] ),
_caseTeleshopping = 1
)
),
[SALES ONLY BA] >= MAX ( 'WEEKLY INCENTIVES'[BA. A NECESSARY] )
),
" :heavy_dollar_sign: Cobra, has " & ROUND ( [POINTS], 1 ) & " points and superavit of "
& [SALES ONLY BA] - MAX ( 'WEEKLY INCENTIVES'[BA. A NECESSARY] ) & "sales",
IF (
AND (
[SALES ONLY BA] >= MAX ( 'WEEKLY INCENTIVES'[BA. A NECESSARY] ),
[SALES ONLY BA] > 0
),
":cross_mark:No cobra, has " & ROUND ( [POINTS], 1 ) & " points and superavit of "
& [SALES ONLY BA] - MAX ( 'WEEKLY INCENTIVES'[BA. A NECESSARY] ) & "sales",
":cross_mark:No cobra, has " & ROUND ( [POINTS], 1 ) & " points and missing "
& ABS ( MAX ( 'WEEKLY INCENTIVES'[BA. A NECESSARY] ) - [SALES ONLY BA] ) & "sales"
)
),
BLANK ()
)
gran trabajo¡ De esta manera ha bajado significativamente el tiempo de carga.
Muchas gracias
Por favor, pruebe esta fórmula. Puede ser más rápido.
RANKING POINTS FAST =
IF (
ISFILTERED ( 'TABLE OF DATES'[Nº WEEK AND MONTH] )
|| ISFILTERED ( 'TABLE OF DATES'[DAY MONTH] ),
VAR SalesOnlyBA = [SALES ONLY BA]
VAR MaxBANecessary =
MAX ( 'WEEKLY INCENTIVES'[BA. A NECESSARY] )
RETURN
IF (
SalesOnlyBA >= MaxBANecessary,
IF (
CALCULATE (
RANKX ( ALL ( 'AGENTS AND SCHEDULES'[AGENT] ), [POINTS],, DESC, SKIP ),
'WEEKLY INCENTIVES'[SHIFT ONL/RFZ/TLV] = "ONLINE"
) = 1
|| CALCULATE (
RANKX ( ALL ( 'AGENTS AND SCHEDULES'[AGENT] ), [POINTS],, DESC, SKIP ),
'WEEKLY INCENTIVES'[SHIFT ONL/RFZ/TLV] = "REINFORCEMENT"
) = 1
|| CALCULATE (
RANKX ( ALL ( 'AGENTS AND SCHEDULES'[AGENT] ), [POINTS],, DESC, SKIP ),
'WEEKLY INCENTIVES'[SHIFT ONL/RFZ/TLV] = "TELESHOPPING"
) = 1,
" :heavy_dollar_sign: Cobra, has " & ROUND ( [POINTS], 1 ) & " points and superavit of " & SalesOnlyBA - MaxBANecessary & "sales",
IF (
SalesOnlyBA > 0,
":cross_mark:No cobra, has " & ROUND ( [POINTS], 1 ) & " points and superavit of " & SalesOnlyBA - MaxBANecessary & "sales",
":cross_mark:No cobra, has " & ROUND ( [POINTS], 1 ) & " points and missing "
& ABS ( MaxBANecessary - SalesOnlyBA ) & "sales"
)
)
)
)
Saludos
Equipo de soporte de la comunidad _ Jing
Si esta publicación ayuda, por favor acéptala como Solución para ayudar a otros miembros a encontrarla.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.