Measure Efficiency -- Get distinct values, average per single date, then sum the averages

[ryan25r9]

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.

We need to get distinct values per Employee & Date because the TLG table also has a more granular line level, and Workday Days is just per Employee & Date Worked.

The general idea here is that two different employees can have a different workday per date, because one might be in a country that has different holidays or weekends from the other. So in order to get the real total workdays for "Hours per Day" denominators etc, I need to find average workdays for each date.

EVALUATE

VAR DistinctEmpDays = 

		SUMMARIZE(
        		TLG
                , TLG[Date Worked], TLG[Employee ID], TLG[Workday Days]
        )
        
VAR AvgDays = /* Average workdays per single day */

        GROUPBY(
        		DistinctEmpDays
                , [Date Worked]
                , "AvgWorkdays"
                , AVERAGEX( CURRENTGROUP(), [Workday Days] ) 
        )
        
VAR Total = /* Sum daily avg for total workdays */

		SUMX( AvgDays, [AvgWorkdays] )
                
RETURN  
		ROW("Test", Total)

[v-lionel-msft]

Hi @ryan25r9 ,

Or like this?

Measure = 
VAR x = 
AVERAGEX(
    FILTER(
        TLG,
        TLG[Date Worked] = SELECTEDVALUE(TLG[Date Worked])
    ),
    TLG[Workday Days]
)
RETURN
ROW(
    "Test",
    SUMX(
        TLG,
        x
    )
)

Best regards,
Lionel Chen

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

[ryan25r9]

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

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[ryan25r9]

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)

[mahoneypat]

Thanks for providing sample data and output.  This measure should work for you in a table with Dates on the rows.

NewMeasure =
VAR __summary =
ADDCOLUMNS (
SUMMARIZE ( TLG, TLG[Employee ID], TLG[Date Worked] ),
"AvgHrs", CALCULATE ( AVERAGE ( TLG[Workday Days] ) )
)
RETURN
AVERAGEX ( __summary, [AvgHrs] )

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[ryan25r9]

Greatly appreciate the reply! Unfortunately this measure is in an SSAS tabular model, so I need it to work in all contexts and can't limit to a table with dates on the rows.

[mahoneypat]

Please try this measure instead.

NewMeasure = Sumx(Values(TLG[Date]), calculate(average(TLG[Workday Days])))

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[ryan25r9]

Thank you, but this won't work. It doesn't change the granularity to Employee & Date Worked, so it's aggregating millions of duplicates.