Forum Discussion

SeanI's avatar
SeanI
New Member
3 years ago
Solved

Multiple What-If Parameters slowing down model

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]))
Need Help
  • Mikelytics's avatar
    Mikelytics
    3 years ago

    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.

     

     

     

2 Replies

  • Mikelytics's avatar
    Mikelytics
    Resident Rockstar
    3 years ago

    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.