DAX Measure Efficiency

[dkaise4]

Hello friends! 

I am working through a complex project with a client and I would like to speed up my report loading times... but I am unsure if I am able to given the requirements and structure:

There are 10 different measures which are given a score, feeding into an overall project score with conditional formatting everywhere...

The scoring (Green = 2, Yellow = 1 and Red = 0) as is the Scoring Criteria (e.g. what defines green, yellow and red) is all controlled via their CRM platform through field references. Each measure also has the ability to be over written at the record level. 

I am currently using SUMX to pull in the Green, Yellow and Red score values from the Power BI Scoring Table in CRM. This causes issues when I roll this up to the Project Score by getting the Sum of the 10 different measures. 

Is there a more effective way of computing the overall project score?

Measure Score:
MEASURE _Measures[Actual: Revenue (Score)] = 

VAR actual_weight =

SUM(
    pbi_scorings[actual_weight]
)

VAR actual_green_score = 

SUM(
    pbi_scorings[actual_score_green]
)

VAR actual_yellow_score = 

SUM(
    pbi_scorings[actual_score_yellow]
)

VAR actual_red_score = 

SUM(
    pbi_scorings[actual_score_red]
)

VAR actual_upper_criteria = 

SUM(
    pbi_scorings[actual_revenue_criteria_upper]
)

VAR actual_lower_criteria = 

SUM(
    pbi_scorings[actual_revenue_criteria_lower]
)

VAR measure_calculation =

IF(
    [Actual: Revenue (Scoring Criteria)] = "Missing Value",
    0,
    [Actual: Revenue (Scoring Criteria)]
)

VAR scoring_criteria =

IF(
    measure_calculation >= actual_upper_criteria, actual_green_score, 
    IF(
        measure_calculation >= actual_lower_criteria && measure_calculation < actual_upper_criteria, actual_yellow_score,
        IF(
            measure_calculation < actual_lower_criteria, actual_red_score, actual_red_score
        )
    )
)

VAR score_override =

SWITCH(
    SUM(
        record-table[pbi_scoreoverride_actual_revenue]
    ),
    801220000,actual_red_score,
    801220001,actual_yellow_score,
    801220002,actual_green_score,scoring_criteria
)
RETURN
score_override * actual_weight

Project Score = 
Sum of 10x Measures listed above

[Anonymous]

Yes, that is what I would do. See if it helps.

[Anonymous]

One way I'm thinking of is to build a bridge table that holds the sum values.  Then you can have separate calculations to reference to that table. 

[dkaise4]

Interesting, So I would summarize the values I need in a Calculated Table within the model, then only reference that information for all of my measures?

I am new to 'Bridge Table' any related information would be really helpful for me!