cancel
Showing results 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

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

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

 Measure # times reference Sum level depth CT-MR por Periodo 49 112 CT-MR por Periodo 2 32 67 DO por Periodo 31 66

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 # reference Sum level Peso / CR por Periodo 34 100 Peso / CT-MR por Periodo 35 102 Peso / DO por Periodo 34 100

 Peso / CT-MR por Periodo '=VAR CT_MR_Por_Periodo_2 = [CT-MR por Periodo 2]RETURNIFERROR( 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]RETURNIFERROR( 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]RETURNIFERROR( 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 used Sum of Level SNota Eficiência 131 325
 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

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

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?

Microsoft Employee

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.

Announcements

#### 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 Monthly Update - August 2024

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

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors