Iterative solution replacement in powerbi

Hi mleepin

The logic you've described sounds exactly right - the question is how to implement this with DAX in Power BI. In this case we need to filter both players and dates per player as you've described.

Here is how I would do it (pbix link).

AVG_INITIAL_SCORE = 
VAR PlayersToInclude =
    FILTER ( VALUES ( Scores[PLAYER] ), CALCULATE ( COUNTROWS ( Scores ) ) > 1 )
RETURN
    CALCULATE (
        AVERAGE ( Scores[SCORE] ),
        GENERATE ( PlayersToInclude, FIRSTDATE ( Scores[DATE] ) )
    )

AVG_PROCEEDING_SCORE = 
VAR PlayersToInclude =
    FILTER ( VALUES ( Scores[PLAYER] ), CALCULATE ( COUNTROWS ( Scores ) ) > 1 )
RETURN
    CALCULATE (
        AVERAGE ( Scores[SCORE] ),
        GENERATE (
            PlayersToInclude,
            EXCEPT (
                CALCULATETABLE ( VALUES ( Scores[DATE] ) ),
                FIRSTDATE ( Scores[DATE] )
            )
        )
    )

EDIT: After some thought, the AVG_PROCEEDING_SCORE measure can be written a little more briefly:

AVG_PROCEEDING_SCORE v2 =
CALCULATE (
    AVERAGE ( Scores[SCORE] ),
    GENERATE (
        VALUES ( Scores[PLAYER] ),
        EXCEPT (
            CALCULATETABLE ( VALUES ( Scores[DATE] ) ),
            FIRSTDATE ( Scores[DATE] )
        )
    )
)

The initial FILTER produces a list of Players with more than 1 row.

The GENERATE function used as a filter argument within CALCULATE gives us either combinations of players and their first dates, or combinations of players and all dates but their first date.

There may be other approaches but this is how I would do it.

Regards,

Owen

Hi again mleepin

Yes, I'd forgotten that FIRSTDATE and LASTDATE don't always like duplicate dates.

We can fix this by using FIRSTNONBLANK and LASTNONBLANK instead.

Edited pbix file here.

Within your pbix file, I created these modifiedmeasures:

AVG_INITIAL_SCORE FIX = 
VAR PlayersToInclude =
    FILTER ( VALUES ( Sheet3[Player]), CALCULATE ( COUNTROWS ( Sheet3) ) > 1 )
RETURN
    CALCULATE (
        AVERAGE ( Sheet3[Score]),
        GENERATE ( PlayersToInclude, FIRSTNONBLANK ( Sheet3[Date], 0 ) )
    )

AVG_PROCEEDING_SCORE FIX = 
VAR PlayersToInclude =
    FILTER ( VALUES ( Sheet3[Player]), CALCULATE ( COUNTROWS ( Sheet3) ) > 1 )
RETURN
    CALCULATE (
        AVERAGE ( Sheet3[Score] ),
        GENERATE (
            PlayersToInclude,
            EXCEPT (
                CALCULATETABLE ( VALUES ( Sheet3[Date] ) ),
                FIRSTNONBLANK ( Sheet3[Date], 0 )
            )
        )
    )

These gives the same results as your measures at a total level.

One thing with having MIN_DATE_PER_PLAYER as a calculated column is that the min date won't respond to filter context.

Anyhow, hopefully that's useful and will leave it to you to compare the measures :)

Best regards,

Owen

Thank You - following a test these calcs. that you have confirmed both work as expected.  

Regards

ML.

Hi mleepin

Apologies I missed your follow-up query.

I take it you've solved it already with that measure?

Regards,

Owen