Forum Discussion

Ignacio_Vidal's avatar
Ignacio_Vidal
Frequent Visitor
9 years ago

Displaying measures via SWITCH function affects performance

  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. ...
Need Help
Anonymous's avatar
Anonymous
Not applicable
9 years ago

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's avatar
    Ignacio_Vidal
    Frequent Visitor
    9 years ago

    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!

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      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's avatar
        Ignacio_Vidal
        Frequent Visitor
        9 years ago

        Hi Anonymous, 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!