Displaying measures via SWITCH function affects performance

[Ignacio_Vidal]

Hi everybody.

I'm developing a project with more than 200 measures and 100K rows (and is intended to support 14M rows in the future), so any performance improvement is a big thing.

I have a visual table that displays 30 measures in 3 columns, each one created via a SWITCH function to associate every measure to its own KPI. The problem is, I have two very similar measures for every KPI, lets gonna call them the original and the new one; when I compare its performance one by one in visual KPIs seems to have the same load time, but when displayed in the table, the new one takes much longer than the original, like 1 second more for every measure displayed (and the original table only takes 5 seconds to refresh the 30 measures!).

When I tried to display the 30 new measures at once, the program even runs out of memory, and its in 64 bit version with 10.000MB of memory allowed.
I see totally illogical that two measures with a similar load time, change so much when displayed via a SWITCH function.

Any possible explanation/suggestion?

Thanks.

[Ignacio_Vidal]

Hi everybody.

I'm developing a project with more than 200 measures and 100K rows (and is intended to support 14M rows in the future), so any performance improvement is a big thing.

I have a visual table that displays 30 measures in 3 columns, each one created via a SWITCH function to associate every measure to its own KPI. The problem is, I have two very similar measures for every KPI, lets gonna call them the original and the new one; when I compare its performance one by one in visual KPIs seems to have the same load time, but when displayed in the table, the new one takes much longer than the original, like 1 second more for every measure displayed (and the original table only takes 5 seconds to refresh the 30 measures!).

When I tried to display the 30 new measures at once, the program even runs out of memory, and its in 64 bit version with 10.000MB of memory allowed.
I see totally illogical that two measures with a similar load time, change so much when displayed via a SWITCH function.

Any possible explanation/suggestion?

Thanks.

[v-shex-msft]

Hi @Ignacio_Vidal,

Can you provide some detail content your these measures which you used? It is hard to find out some effictive solution from your descritpion.

Regards,

Xiaoxin Sheng

[Ignacio_Vidal]

Hi v-shex-msft. 

I have two different FactTable and 8 dimension tables, three of them associated to both fact tables.

My dashboard consists in three visual tables with 70 measures each one. The measures are the sum of a column with specific date conditions: the customer can choose a range of dates or a "Year to Date" date.
Some of the measures sum the records who are in that range, other measures sum the records who are in the last full week (monday-sunday) before the last date of the range, other measures sum the records who are in the equivalent range in the last year, and so on.
Of course, not all the measures sum the same column, and even when they do, usually they have different conditions, like an specific value in a different column.
In addition, some measures divide a column of one FactTable with a column of the other FactTable.

I have created all my measures with the structure of a SUMX(filter(FactTable, filter1 && filter2 && date restriction, etc), ColumnToSum)

I know CALCULATE is faster, but the date restrictions force me to use the filter function, because they're variable restrictions.

I think I'm not making myself very clear. The point is, my main concern is how displaying a measure by a SWITCH function can affect performance.

Thanks!

[v-shex-msft]

Hi @Ignacio_Vidal,

Sorry for slow response, but I still not found the documents which mentioned the performance cost of switch function.

In my opinion, I think the performance issue more related to your filters which written in the measure. If you use this measure calculate on large amount of records, it may take more resource than single measure.

>>I know CALCULATE is faster, but the date restrictions force me to use the filter function, because they're variable restrictions.

Calculate also support multiple filters on it.

Regards,

Xiaoxin Sheng

[Ignacio_Vidal]

Hi @v-shex-msft, thanks a lot for your effort.

I will try to change some measures to calculate then. Good news are that I found some performance improvements that made my program work again, so the SWITCH problem, yet unknown, is not so important now. If I found any reason that justifies this behaviour, I will post it in here.

Kudos to you!

[DoubleJ]

200 measures are alot! Probably it's possible to reduce the number of measures using a more appropriate model design. That should lead to a better performance as well.

[Ignacio_Vidal]

Hi DoubleJ, thanks for the reply!

Unfortunately its not possible. The client requests more than 200 different KPIs in the same dashboard, eachone with different dates and filter conditions, so my only option is to do whatever its possible to reduce load times.

Thanks for your answer, anyway 🙂