Writing a more efficient DAX measure

[Matthias93]

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

[Baskar]

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 

[Baskar]

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]

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 · Accepted solution]

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

[Parihar1980]

Please help me to solve this

Select Count(distinct) , Col From Table

Group by Col 

i am not getting result with below expression 

=SUMMARIZE(Table , Table[Col] ,

Countrows(DISTINCT(table[Col])))

[srinivaspappu22]

Hello Parihar,

Have you got solution for your query ? If so please share the DAX query for the same.

Thanks,

Srini.

[srinivaspappu22]

How to code the below SQL in DAX / Measure / New column in PowerBI Report ? Please help.

SELECT COL2, COUNT(COL1) FROM TABLE1 Group By COL2