Power Bi: formula similar to Excel's MAXIFS with multiple criteria

Question

In Power Bi I have a similar Table and I'm trying to figure out a way to calculate in column "G" the maximum value (only for Gender F) for each Team and each Day.

In Excel I would use something like this (although I would still need to figure out a way to manage duplicated values - rows highlighted in yellow)

=IF(AND([@Gender]="F";MAXIFS([Value];[Day];[@Day];[Team];[@Team];[Gender];"F") = [@Value]);MAXIFS([Value];[Day];[@Day];[Team];[@Team];[Gender];"F");"")

Anyway, I've written the Excel formula only to explain better the desiderd outcome, I do know that DAX's logic is different, but I can't find a way to make this work in Power Bi. I've managed to obtain the maximum value, but it's written on each row of the column, which is not what I want.

I've used this formula and I know that I should apply additional filters but my knowledge of DAX is close to none ...

Max Value = CALCULATE(MAX('MyTable'[Value]), ALLEXCEPT( 'MyTable', 'MyTable'[Team&Day]))

'MyTable'[Team&Day] is a column I've created in Power Bi by joining columns Team and Day

Any help is appreciated, thanks!

Answer 1

For a calculated column, the crucial tricks are to catch the current row values in variables, then use the filter parameters of the CALCULATE function to apply them. Along the lines of:

Max Value =
VAR v_Team = [Team]
VAR v_Day = [Day]
RETURN
    CALCULATE (
        MAX ( 'MyTable'[Value] ),
        'MyTable'[Team] = v_Team,
        'MyTable'[Day] = v_Day,
        'MyTable'[Gender] = "F"
    )