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.

[Ahmedx]

try this code

efficency iterative  = 
VAR _tbl = CROSSJOIN(
    VALUES(dim_date[date]),VALUES(dim_department[departmentKey]))
    RETURN

    AVERAGEX(_tbl
        ,
        [efficiency])
---------------or-----------
efficency iterative  = 
AVERAGEX(
    CROSSJOIN(
        VALUES(dim_date[date]),
        VALUES(dim_department[departmentKey])
    ),
    [efficiency]
)

[vojtechsima]

Hey, @Ahmedx 

EDIT: In the provided .pbix it works.

Thanks for suggestions, this is almost working, unfortunately the total (in matrix or outside context dim/date) isn't completely correct.

[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?

[vojtechsima]

Well, @lbendlin , I was just wondering if you can do this somehow "nicer" and more efficient, as AVERAGEX inside AVERAGEX isn't considered nice code, let's say.

[lbendlin]

There's a lot of shades of grey here. "Nice"  versus "functional" versus "scalable"  versus "maintainable"  etc etc.

If you don't like the code but it performs well then you may be trying to solve the wrong problem. If the code is performing badly then your time spent on improving it is more valuable.

[vojtechsima]

All right, sure, the code works fast and well, buuut I was wondering if there's another way, but seems it stay as it is. 

[gmsamborn · Accepted solution]

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.

[vojtechsima]

@gmsamborn  thanks, my question was if there's a more efficient way to write the double iteration measure. 

Your solution works, the speed seems similar, I'll test it in my original solution. 

Thanks regardless.