Parameter performance

[SanderVeeken]

Hi everyone,

I've been building a multilingual report and am using parameters to facilitate this. It's a report of survey responses and in essence the data looks like this:

I have a whole bunch of dim tables that have the different (standard) response options in different languages, a list of submissions that link to the dim tables by question-option-id and a separate translation table that has question titles in various languages and some generic labels.

It all works really well and I'm happy to say I've been able to create a completely dynamic report. My only issue is performance. I have a dataset of less than 30k rows and although there are a lot of dim tables, mostly I'm just counting submissions. When I look at the performance analyzer, I'm averaging between 3 and 4 seconds for a visual with peaks up to 6, and the major culprit is "evaluating parameters". My parameters looks like this:

P_question1 = 
VAR _ID = "Q_question1"
VAR _en = LOOKUPVALUE('TranslationTable'[label_en], 'TranslationTable'[ID], _ID)
VAR _es = LOOKUPVALUE('TranslationTable'[label_es], 'TranslationTable'[ID], _ID)
VAR _fr = LOOKUPVALUE('TranslationTable'[label_fr], 'TranslationTable'[ID], _ID)

RETURN
{
    (_en, NAMEOF('question1'[q1_response_en]), 0, "en"),
    (_es, NAMEOF('question1'[q1_response_es]), 1, "es"),
    (_fr, NAMEOF('question1'[q1_response_fr]), 2, "fr"),
}

except that I'm doing it with 6 languages and obviously this is dummy data.

The "evaluating parameters" portion seems to take between 2 and 3 seconds of each visual's loading time. My connection is DirectQuery to AAS, but I already tried directly importing the translation table and noticed little difference - since I'm looking up all the column headers I figured it might be quicker if imported, but alas...

Does anyone have any idea how to improve this, or is it just a fact of life when using so many parameters (I must have about 40/50 in total for the entire report, with maybe a max of 15 per page).