Slow Table

[Anonymous]

Hello,

I have a table that takes forever to load. Anytime I use any slicers, same thing applies (SLOW to load). To my understanding, this is largely due to the columns in my table being comprised of measures with code that looks like this:

The reason I have the COUNTROWS function is to fix my totals row. Prior to implementing this logic, my totals row would always be incorrect.

My question is:

How can I increase the speed of my table? Is there a better way to fix my totals line?

Thanks,

Joel

[amitchandak]

@Anonymous , please provide the measure in text format. Also share logic what you are trying to build

[Anonymous]

forecasted_base_incentive =

//if, based on distributor name column, we are looking at a distributor... do the below

IF(COUNTROWS(VALUES('gwp_forecast_master'[distributor_name (groups)]))=1,

[forecasted_base_incentive_%]*[forecasted_2022_sales],

//if, based on the distributor name column, we are looking at the TOTAL line, then do the below (sum the above)

SUMX(VALUES('gwp_forecast_master'[distributor_name (groups)]),

[forecasted_base_incentive_%]*[forecasted_2022_sales]))

//The extra "COUNTROWS... code ensure that our totals are correct. When using measures as columns, the total line will be incorrect unless this logic is incorporated.

LOGIC: 

NOTE: I pasted a different measure involved in the table to be a bit more obvious. I have a table with about 6 columns that are all measure-based. AS such, I was getting an inaccurate total row. So, I added the SUMX and other logic to fix the total row. However, this has cost me major computing speed. The table is very slow. Let me know if you need additional info.

[AlexisOlson · Accepted solution]

Using just the SUMX part should cover both cases (since it would only sum over one group for the non-total lines).

forecasted_base_incentive =
SUMX (
        VALUES ( 'gwp_forecast_master'[distributor_name (groups)] ),
        [forecasted_base_incentive_%] * [forecasted_2022_sales]
    )

How well this measure performs is mostly dependent on how well each of the measures you're multiplying together performs. What are the definitions for these?

[Anonymous]

Alexis,

You just made my day! Everything is updating very quickly again. Such a simple solution, but very powerful.

Thanks again,

Joel