Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
dkaise4
New Member

DAX Measure Efficiency

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

3 REPLIES 3
Anonymous
Not applicable

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

Anonymous
Not applicable

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. 

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!

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.