Optimize double iteration measure in DAX

[vojtechsima]

Hello,

I'd like to know how I can optimize this measure. The goal is to eliminate double iteration while keeping the logic.

efficency iterative = 
AVERAGEX(
    VALUES(dim_date[date]),
    AVERAGEX(
        VALUES(dim_department[departmentKey]),
        [efficiency])
    )

Some notes:

• I cannot rewrite the [efficiency] inside the measure, it's too complex.
• I wanna eliminate this double iteration (AVERAGEX in AVERAGEX).
• The solution cannot be slower.
• Efficiency portrayed in the example is not real efficiency; that's quite complex. But for this example, we can work with efficiency as defined in the example.

Logic:
Calculate efficiency for the Department and then average the results of each department's efficiency throughout the day. 

 File in attachment. Thanks a lot.

[lbendlin]

The goal is to eliminate double iteration while keeping the logic.

aka "wash me but don't make me wet"

If you need the average of averages then you cannot avoid double iteration.  Best you can do is reduce cardinality in each iteration.

Have you checked the query plan in DAX Studio?

[gmsamborn]

HI @vojtechsima 

I'm nnot sure i really understood the question.

Would a measure like this help?

MyEfficience = 
VAR _Table =
    SUMMARIZE(
        'fact',
        'fact'[dateKey],
        'fact'[departmentKey],
        "__Eff", CALCULATE( [efficiency] )
    )
VAR _Result =
    AVERAGEX(
        _Table,
        [__Eff]
    )
RETURN
    _Result

cleaner_dax_with_iterators_json - mine.pbix

Please let me know if you have any comments/questions.