Dynamic Weighted Average DAX

[as1195]

Hi ,
We have a requirement wherein based on measure results and availability(not blank) will have to calculate score as per the given weightage shown below
Note : All the measures have result of -1,1 and 0
Ex:If there are no blanks for any of the measures then will have to consider "All Present" case  and 
multiply each measure result with therir corresponding weightage as per the "All Present" row and sum the scores.

Currently I am using If statements but since there are many loops ,it is causing performance delay.
Please Advice!
Thanks in Advance!

[Anonymous]

Hi @as1195 ,

You can try to use switch, which may perform better. Here I will provide you with a simplified example:

Weighted_Score =
VAR All_Present = NOT(ISBLANK('Table'[Measure1])) && NOT(ISBLANK('Table'[Measure2]))
VAR 1_Missing = ISBLANK('Table'[Measure1]) && NOT(ISBLANK('Table'[Measure2]))
VAR 2_Missing = NOT(ISBLANK('Table'[Measure1])) && ISBLANK('Table'[Measure2])

RETURN
SWITCH(
    TRUE(),
    All_Present, 'Table'[Measure1]*[Weight1] + 'Table'[Measure2]*[Weight2],
    1_Missing, 'Table'[Measure2]*[Weight2],
    2_Missing, 'Table'[Measure1]*[Weight1],
    0
)

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[as1195]

Thank You for your reply.
I tried with switch but that also seems to be taking same amount of time.

[Anonymous]

Hi @as1195 ,

I suggest you to download DAX studio to check the timestamp used for each DAX statement.
It can tell you which statements in the DAX you are using are consuming more time and you can try to modify those statements.

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.