Forum Discussion

Matthias93's avatar
Matthias93
Helper III
9 years ago
Solved

Writing a more efficient DAX measure

Hi,

 

I recently wrote a DAX measure based on some help I got on this forum. However I now see that these measures are very consuming in terms of memory and slow my report down way too much. I have a table with the following structure: I have one line per person giving me the name of that person and answers to 4 questions. Question 1 and 2 are values between 1 and 5 and question 3 and 4 are either 1 or 0. 

 

Another column contains is the number of hours related to the performance snapshot. Each question has a different 'weighted score' table where it has a column with the name of the employee and the weighted score for a question. This weighted score is calculated the same way for each question. See the pictures below for more information. The first one shows what I want to achieve with the weighted questions. The second one shows how it's being done at the moment.

 

It would be really great if somebody could help we with this, I have very little experience with DAX and I'm getting pretty desperate.

 

Kind regards,

Matt

ObjectiveCurrent DAX

Need Help
  • Baskar's avatar
    Baskar
    9 years ago

    Mr. Matt , 

     

     

    Try this one surely it will help you , if not let me know i will help u 

     

     

    Weighted Question = var team_Manger = 'Question Answer'[Team Member]

    var Total_Hours = CALCULATE(SUM('Question Answer'[Number of Hours]),FILTER(ALL('Question Answer'),'Question Answer'[Team Member]=team_Manger))

    return ('Question Answer'[Number of Hours]/Total_Hours) * 'Question Answer'[Question 1 Value]

     

    Create as Calculated Column 

    Cal ColumnOutput

7 Replies

  • Baskar's avatar
    Baskar
    Resident Rockstar
    9 years ago

    Am not getting ur expected Answer .

     

    if u don't mind can u pls explain me what is in "Weight Question One Value " in Excel 

     

    (30/64)*4 + (30/64)*2 

     

    How u getting this , 

    i think this is u want as a output in DAX.

    • Matthias93's avatar
      Matthias93
      Helper III
      9 years ago

      I am indeed looking for a dax function that gives me the weighted value for the question. So if a person has for example a '2' on a 10 hour project and a '4' on a 20 hour project the dax measure should give me: [(2*(10/30)) + (4*(20/30))]. 30 being the total amount of hours. What do you mean exactly by Team_Manger?

       

      Thanks for helping

       

      Kr,

      Matt

      • Baskar's avatar
        Baskar
        Resident Rockstar
        9 years ago

        Mr. Matt , 

         

         

        Try this one surely it will help you , if not let me know i will help u 

         

         

        Weighted Question = var team_Manger = 'Question Answer'[Team Member]

        var Total_Hours = CALCULATE(SUM('Question Answer'[Number of Hours]),FILTER(ALL('Question Answer'),'Question Answer'[Team Member]=team_Manger))

        return ('Question Answer'[Number of Hours]/Total_Hours) * 'Question Answer'[Question 1 Value]

         

        Create as Calculated Column 

        Cal ColumnOutput

  • Baskar's avatar
    Baskar
    Resident Rockstar
    9 years ago

    Hi matt, 

     

     

    Create one Calculated Column on your Table and apply this below logic 

     

     

    Weighted Question = var team_Manger = 'Question Answer'[Team Member]

    var Total_Hours = CALCULATE(SUM('Question Answer'[Number of Hours]),FILTER(ALL('Question Answer'),'Question Answer'[Team Member]=team_Manger))

    return ('Question Answer'[Number of Hours]/Total_Hours) * 'Question Answer'[Question 1 Value]

     

    Note : 

    Replace Table_Name and Column based on your Requierment.

     

     

    Let me know if not solve your problem , Cheers