Multiple What-If Parameters slowing down model

[SeanI]

Hi All!

I'm struggling with a recent model that uses multiple What if parameters to allow the user to forecast future employee head counts. My data table is a list of current employees that fall within various category combinations and the user wants to be able to project each of these category combinations individually over a multiple year projection. I've created an example .pbix that shows the issue I'm running into but I don't see an option to upload attachments. Does anyone know how I can do this?

In this example, each employee falls into a category combination of country, state and level. I only included the first 6 category combinations and a year 1 projection for the example, but my real use-case includes over 400 different combinations of categories, resulting in over 400 what-if parameters. In that dashboard, the updates to visuals (bar charts, tables, matrix) now takes over a minute to update with every change to a parameter.

My questions are:

Is there a better way than multiple what-if parameters to allow the user control over selecting their estimate for head counts at each combination of categories?

Is my measure for Year 1 projection not efficient and that's what is causing the slowdown?

Is there a better way to go about this than my current model design?

Thank you in advance for the help and guidance!

Below is the measure I'm currently using for my year 1 projection.

Year 1 =

var CAExperienceHire

    = SUM('Data Table'[Current Count]) +('CA Experienced Hire'[CA Experienced Hire Value])

var CAIntern

    = SUM('Data Table'[Current Count]) +('CA Intern'[CA Intern Value])

var CANewHire

    = SUM('Data Table'[Current Count]) +('CA New Hire'[CA New Hire Value])

var CAProfessional

    = SUM('Data Table'[Current Count]) +('CA Professional'[CA Professional Value])

var CASemiPro

    = sum('Data Table'[Current Count]) +('CA Semi-Pro'[CA Semi-Pro Value])

var CASubjectMatterExpert

    = sum('Data Table'[Current Count]) +('CA Subject Matter Expert'[CA Subject Matter Expert Value])

    Return SWITCH(TRUE(),

average('Data Table'[Mapping])=1,CAExperienceHire,

average('Data Table'[Mapping])=2,CAIntern,

average('Data Table'[Mapping])=3,CANewHire,

average('Data Table'[Mapping])=4,CAProfessional,

average('Data Table'[Mapping])=5,CASemiPro,

average('Data Table'[Mapping])=6,CASubjectMatterExpert,sum('Data Table'[Current Count]))

[SeanI]

Thanks @Mikelytics I will definitely try that first. I also upaloded my pbix to OneDrive below, I think 🙂

 https://1drv.ms/u/s!AjHltOOoJ9vnjRCVBddhBH8dlPYu?e=YgKpVb 

[Mikelytics · Accepted solution]

Hi @SeanI 

Can you please try to also put...

SUM('Data Table'[Current Count]) 

... in a separate variable.

It might be that in each measure this is calcualted multiple time instead of one time. So the formulal would look like:

Year 1 =

var var_Sum = SUM('Data Table'[Current Count])

var CAExperienceHire
    = var_Sum +('CA Experienced Hire'[CA Experienced Hire Value])
var CAIntern
    = var_Sum +('CA Intern'[CA Intern Value])
var CANewHire
    = var_Sum +('CA New Hire'[CA New Hire Value])
var CAProfessional
    = var_Sum +('CA Professional'[CA Professional Value])
var CASemiPro
    = var_Sum +('CA Semi-Pro'[CA Semi-Pro Value])
var CASubjectMatterExpert
    = var_Sum +('CA Subject Matter Expert'[CA Subject Matter Expert Value])

Return 
SWITCH(TRUE(),
    average('Data Table'[Mapping])=1,CAExperienceHire,
    average('Data Table'[Mapping])=2,CAIntern,
    average('Data Table'[Mapping])=3,CANewHire,
    average('Data Table'[Mapping])=4,CAProfessional,
    average('Data Table'[Mapping])=5,CASemiPro,
    average('Data Table'[Mapping])=6,CASubjectMatterExpert,var_Sum))

So if I am right there should be less redundant calculation. But also if it does not speed up your measure it still simplifies it 🙂

Best regards

Michael

-----------------------------------------------------

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your thumbs up!

@ me in replies or I'll lose your thread.