Forum Discussion

as1195's avatar
as1195
Helper I
2 years ago

Dynamic Weighted Average DAX

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!



Need Help

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      as1195
      Helper I
      2 years ago

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        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.