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
felipeveiga
Regular Visitor

Performance Problem - Converting Measure to Column

Scenario: Evaluating personal quality, by different metrics.

For example if John reported 3 cases and he is from accounting. If accountants report 3 cases, they got 100%, therefore John . Felipe reported 30 cases and he is from HR. If HR report 60 cases, they got 60, therefore Felipe got 50% of the score.

BUT John also works in the HR. At the HR John reported 72 cases, therefore he got 100% (more than 60).

 

Problem: Most of the data was calculated on the fly through measures.  But when getting to the final evaluation takes too long to complete because of nested measures and codependencies.

 

What is the best way to fix this?

(At the moment, we are getting the data, exporting it in CSV and reloading it in PowerBI so we can get the final calculations).

 

General Idea

Count the times a report happened 

Measure# times referenceSum Level Depth
Aprovado Direto Ponderado 322
Aprovado Parcial Ponderado 322
Aprovado Total Ponderado 31525
Aprovado Preliminar Ponderado 322
Aprovado Total Nao Pesado22

 

 

Aprovado Direto Ponderado 3'=sum('FACT-Aprovados-Direto-Agrupado-por-ID'[Aprovado-Direto-Pesado])
Aprovado Parcial Ponderado 3'=SUM('FACT-Aprovados-Parcial-Agrupado-por-ID'[Aprovado-Parcial-Ponderado])
Aprovado Preliminar Ponderado 3'=SUM('FACT-Aprovados-Preliminar-Agrupado-por-ID'[Aprovado-Preliminar-Ponderado])
Aprovado Total Nao Pesado'=SUM('FACT-Aprovados-Total-Agrupado-por-ID'[Aprovados-Total])
Emitiu Parcial Ponderado 2'=SUM('FACT-Emitiu-Parcial'[Emitiu-Parcial-Ponderado])


Then it gets how many days the person has worked in that area and divides by that number

 

Measure# times referenceSum level depth
CT-MR por Periodo49112
CT-MR por Periodo 23267
DO por Periodo3166
CT-MR por Periodo 2'=IFERROR(IF([Enviou Dados?] = "Nao enviou dados", BLANK(),
    CALCULATE([Aprovado Total Ponderado 3] , 'DIM-Modalidade'[Modalidade] = "CT" || 'DIM-Modalidade'[Modalidade] = "MR")/
    IF(SUM('FACT-Medicos-Periodos-Mensal'[Mama-Adm]) > 0, SUM('FACT-Medicos-Periodos-Mensal'[Mama-Adm]), SUM('FACT-Medicos-Periodos-Mensal'[TC-RM-Adm]))), BLANK())
CR por Periodo'=IFERROR(IF(
    [Enviou Dados?] = "Nao enviou dados", BLANK(),
    CALCULATE([Aprovado Total Ponderado 3], 'DIM-Modalidade'[Modalidade] = "CR"
    ) / SUM('FACT-Medicos-Periodos-Mensal'[RX-Adm])), BLANK())
DO por Periodo'=IFERROR(IF(
    [Enviou Dados?] = "Nao enviou dados", BLANK(),
    CALCULATE([Aprovado Total Ponderado 3], 'DIM-Modalidade'[Modalidade] = "DO"
    ) / SUM('FACT-Medicos-Periodos-Mensal'[RX-Adm])), BLANK())

 

Then it divides this measure (DO por periodo) by the area target score and checks where the person belongs to and averages by how many days they worked in that area.

In my example, John would get 100% from accounting and 100% from HR.

 

Measure# referenceSum level
Peso / CR por Periodo34100
Peso / CT-MR por Periodo35102
Peso / DO por Periodo34100

 

Peso / CT-MR por Periodo'=VAR CT_MR_Por_Periodo_2 = [CT-MR por Periodo 2]
RETURN
IFERROR( IF(
    CT_MR_Por_Periodo_2 > MAX('FACT-Pesos-Mensal-Chefes'[E - Produtividade Valor  Maximo por Periodo CT-MR]),
     1,
      CT_MR_Por_Periodo_2 / MAX('FACT-Pesos-Mensal-Chefes'[E - Produtividade Valor  Maximo por Periodo CT-MR])
), BLANK())
Peso / CR por Periodo'=VAR CR_Por_Periodo = [CR por Periodo]
RETURN
IFERROR( IF(
    CR_Por_Periodo > MAX('FACT-Pesos-Mensal-Chefes'[E - Produtividade Valor  Maximo por Periodo CR]),
     1,
      CR_Por_Periodo / MAX('FACT-Pesos-Mensal-Chefes'[E - Produtividade Valor  Maximo por Periodo CR])
), BLANK())
Peso / DO por Periodo'=VAR DO_Por_Periodo = [DO por Periodo]
RETURN
IFERROR( IF(
    DO_Por_Periodo > MAX('FACT-Pesos-Mensal-Chefes'[E - Produtividade Valor  Maximo por Periodo DO]),
     1,
     DO_Por_Periodo / MAX('FACT-Pesos-Mensal-Chefes'[E - Produtividade Valor  Maximo por Periodo DO])
), BLANK())

 

 

Then it sums the other criteria

Measure# References usedSum of Level
SNota Eficiência131325
SNota Eficiência=IF(NOT([Selected Group] = BLANK()),  
    [Nota E Periodos Semanais] + [Nota E Tempo Casa] + [Nota E - Produtividade por Periodo],
    BLANK())

 

Performance and Memory Consumption

 Memory.png

 

 

So what is the best way to "save" the temporary variables" in order to consume less CPU and be able to display on the fly these data? Is it converting the measure to a column? If so, what is the best and painless way to do it?

3 REPLIES 3
v-jiascu-msft
Microsoft Employee
Microsoft Employee

Hi @felipeveiga,

 

The calculated column and calculated table could be the way to store temporary data. But sometimes they wouldn't help. The structure of the data mode is also important to speed up the mode. Can you share a dummy sample? Reference: https://docs.microsoft.com/en-us/power-bi/power-bi-reports-performance.

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Is there any way to instead of creating a calculated table, to store the calculated data?

Is there any way to export with dummy data?

Hi @felipeveiga,

 

Usually, we can use the table functions like SUMMARIZE to store the processing data without creating a calculated table. Maybe you can keep the structure of your data model and some featured data.

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors