Forum Discussion
Measure Efficiency -- Get distinct values, average per single date, then sum the averages
Hey everyone, I'm trying to find a way to refactor this measure to make it more efficient, since it's currently pretty slow. I've been spinning my wheels, so any help is greatly appreciated. ...
Sorry, this doesn't quite get there either. It doesn't account for there being duplicate TLG[Workday Days] per Date Worked & Employee.
Probably helps to give an example. Starting dataset:
| Employee ID | Date Worked | Workday Days | TLP ID |
| 491 | 01/05/20 | 0 | 10 |
| 491 | 01/06/20 | 0.5 | 4 |
| 491 | 01/06/20 | 0.5 | 941 |
| 491 | 01/07/20 | 1 | 331 |
| 491 | 01/08/20 | 1 | 596 |
| 491 | 01/08/20 | 1 | 2537 |
| 11402 | 01/06/20 | 1 | 319 |
| 11402 | 01/06/20 | 1 | 782 |
| 11402 | 01/07/20 | 1 | 331 |
| 11402 | 01/08/20 | 1 | 841 |
| 99821 | 01/05/20 | 0 | 10 |
| 99821 | 01/06/20 | 1 | 319 |
| 99821 | 01/07/20 | 1 | 2537 |
| 99821 | 01/07/20 | 1 | 596 |
| 99821 | 01/07/20 | 1 | 941 |
| 2729 | 01/05/20 | 1 | 331 |
| 2729 | 01/05/20 | 1 | 1931 |
| 2729 | 01/06/20 | 0.5 | 841 |
Distinct values of Employee ID, Date Worked, & Workday Days:
| Employee ID | Date Worked | Workday Days |
| 491 | 01/05/20 | 0 |
| 491 | 01/06/20 | 0.5 |
| 491 | 01/07/20 | 1 |
| 491 | 01/08/20 | 1 |
| 11402 | 01/06/20 | 1 |
| 11402 | 01/07/20 | 1 |
| 11402 | 01/08/20 | 1 |
| 99821 | 01/05/20 | 0 |
| 99821 | 01/06/20 | 1 |
| 99821 | 01/07/20 | 1 |
| 2729 | 01/05/20 | 1 |
| 2729 | 01/06/20 | 0.5 |
AVERAGE Workday Days by day:
| Date Worked | Average Workday Days |
| 01/05/20 | 0.333 |
| 01/06/20 | 0.750 |
| 01/07/20 | 1.000 |
| 01/08/20 | 1.000 |
Lastly, the SUM of averages, making the expected result of the measure here:
| 3.083 |
Greatly appreciate the Power BI file, thanks so much. This actually returns the correct results, but running on the entire model drops the efficiency from 25 seconds to 171 seconds. The core table (TLG) is 700M rows, so that certainly isn't helping with the context transition. For reference, this is the old code simplified and your recommended code altered to run in DAX Studio:
EVALUATE VAR Old = SUMX( GROUPBY( SUMMARIZE(TLG, TLG[Date Worked], TLG[Employee ID], TLG[Workday Days]) , [Date Worked] , "AvgWorkdays" , AVERAGEX( CURRENTGROUP(), [Workday Days] ) ) , [AvgWorkdays] ) VAR New = SUMX ( VALUES ( 'TLG'[Date Worked] ), AVERAGEX ( SUMMARIZE ( VALUES ( TLG[Employee ID] ), [Employee ID], "AvgWorkdays", CALCULATE(AVERAGE(TLG[Workday Days])) ), [AvgWorkdays] ) ) RETURN ROW("test", New)