0

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.

enter image description here

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!

2
  • Do you want to produce a calculated column or a measure?
    – Mike Honey
    Apr 30, 2021 at 10:00
  • It doesn't really matter to me at the moment (though I know it should and I know that there's a basic difference ). But I'm just using PowerBi to create a "cute" report for fun at the moment, so any solution would be good. I've used only columns so far because it's easier for me to instantly check if the outcome is ok and because I almost can't tell the difference between the two or use them appropriately. :)
    – Pesetas74
    Apr 30, 2021 at 16:44

1 Answer 1

0

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"
    )

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.