Many virtual tables making the dashboard extremely slow

[Anonymous]

Hello! 

I'm having a very big problem right now with one of my dashboards because I'm needing to use 28 measures which use virtual tables within to bring me pieces of information according to some rules, and it appears to be the only way the bring the correct values, which are totally responsive to all of the filters presented. The virtual table inside of each measure is the same, but the rules change from measure to measure. 

I calculate 4 percentiles, also with virtual tables, and I use them as rules to find the wanted values. An example is presented below: 

Percentiles used in the measure:

percentile_1_ptr =

PERCENTILEX.INC(

    FILTER(SUMMARIZE(Fato,Fato[cdd_pdv],

"nr_19",CALCULATE(SUM(Fato[netRevnue]),Fato[ano] = 2019),

"nr_20",CALCULATE(SUM(Fato[netRevnue]),Fato[ano] = 2020),

"ptr_growth",(DIVIDE(DIVIDE(CALCULATE(SUM(Fato[ttv_medio_2]),Fato[ano] = 2020),

CALCULATE(SUM(Fato[volume]),Fato[ano] = 2020),0),

DIVIDE(CALCULATE(SUM(Fato[ttv_medio_2]),Fato[ano] = 2019),

CALCULATE(SUM(Fato[volume]),Fato[ano] = 2019),0),0)-1) ),

[nr_19] > 0 && [nr_20] > 0),

    [ptr_growth],0.333

)

percentile_1_vol =

PERCENTILEX.INC(

    FILTER(SUMMARIZE(Fato,Fato[cdd_pdv],

"nr_19",CALCULATE(SUM(Fato[netRevnue]),Fato[ano] = 2019),

"nr_20",CALCULATE(SUM(Fato[netRevnue]),Fato[ano] = 2020),

"vol_growth",(DIVIDE(CALCULATE(SUM(Fato[volume]),Fato[ano] = 2020),

CALCULATE(SUM(Fato[volume]),Fato[ano] = 2019),0)-1)

),

[nr_19] > 0 && [nr_20] > 0),

    [vol_growth],0.333

)

A measure that uses the percentiles above:

Count_Stores =

VAR percent_vol_1 = [percentile_1_vol]

VAR percent_ptr_1 = [percentile_1_ptr]

RETURN

COUNTX(

SUMMARIZE(Fato,Fato[cdd_pdv],

"nr_19",CALCULATE(SUM(Fato[netRevnue]),Fato[ano] = 2019),

"nr_20",CALCULATE(SUM(Fato[netRevnue]),Fato[ano] = 2020),

"ptr_growth",(DIVIDE(DIVIDE(CALCULATE(SUM(Fato[ttv_medio_2]),Fato[ano] = 2020),

CALCULATE(SUM(Fato[volume]),Fato[ano] = 2020),0),

DIVIDE(CALCULATE(SUM(Fato[ttv_medio_2]),Fato[ano] = 2019),

CALCULATE(SUM(Fato[volume]),Fato[ano] = 2019),0))-1),

"vol_growth",(DIVIDE(CALCULATE(SUM(Fato[volume]),Fato[ano] = 2020),

CALCULATE(SUM(Fato[volume]),Fato[ano] = 2019),0)-1)

),

IF([nr_19] >0

&& [nr_20] >0

&& [vol_growth] < percent_vol_1

&& [ptr_growth] < percent_ptr_1 ,1,BLANK())

)

Since this calculations are made on the visual level, it is consuming a lot of memory and taking a huge process time.

Please, HELP! Thanks a lot!

[MFelix]

Hi @Anonymous,

Don't know how your model is setup, but one thing that I have learned when I started to work with virtual tables is that you must avoid making summarization of the full table because this will force your measure to check all the table and then filter out what you need.

You should of possible restringe your summarization to the columns that you need for the calculation using ALL or ALLSELECTED and refering only to the tables you need. 

Can you share some more information about your model maybe a mockup and expected result. 

[Anonymous]

Hello, @MFelix !!

Thanks for the answer! 

I have one full table with the profit, price, and volume information and several other information about the products and the clients, and when I do the summarized table I use only the Client ID column and the profit, price, and volume. When I do this table in the back end, the visual responds correctly to filters related only to the client's characteristics (like region and client category). But when I go the product filters, this table starts to give wrong values, and I need to summarize the information by Client, and the only way that is giving me the write value now is by using virtual tables on the measure. I don't know if there is any other way to achieve the same result as the virtual table. Do you know if there is any other configuration os something else that can prevent me of making the same virtual table for all of the measures?

[MFelix]

Hi @Anonymous ,

Are you using a star schema model with dimension tables for the products, client, regions and so on?

[Anonymous]

Hello, @MFelix!!

Yes! I have a fact table that relates all of the fields to the profit, price, and volume. But if I tried to do a summarized table from the fact table with the client ID and sum of the profit by year, this table just suffers the effect of the filters related to the client id correctly that does not change through the years. Even checking the box for the filters to work on both tables.

[MFelix]

Hi @Anonymous ,

Depending on the way your model is setup you may need or not to summarize on table. But one best practice that I know you must use while making the use of SUMMARIZE is to only refer to the columns you need and not the entire table, because that will increase the number of data that your calculation needs to check.

Are you abble to share a sample file? If information is sensitive please share a mockup or by private message.