How to find the maximum value from a column satisfying two or more IF conditions in DAX

0
votes

I am a newbie to Power BI and DAX.

I have a dataset as attached. I need to find the maximum value for each person for each week. I have written the formula in Excel.

=MAX(IF(A$2:A$32=A2,IF(D$2:D$32=D2,IF(B$2:B$32=1,C$2:C$32))))

How can I convert it to DAX or write the same formula in Power BI? I tried the DAX Code as below, But it did not work(ALLEXCEPT Function expects table). 

Weekly Maximum = 
CALCULATE ( MAX ( PT[Value] ), ALLEXCEPT ( PT, PT[person], PT[Week], 
PT[category] ==1 ) )

Once I calculate this, then I need to calculate the Expected value for each week, that has the maximum value of the previous week * 2.85, as shown in the screenshot. How can I put the previous week's maximum value for this week?

Any corrections/solutions, please? Dataset TIA

1
maxpowerbidax

1 Answers

1
votes

The Max Value for Category 1 can be written like this:

= CALCULATE(MAX(PT[Value]),
            ALLEXCEPT(PT, PT[Person], PT[Week]),
            PT[Category] = 1)

(The Category filter doesn't go inside ALLEXCEPT().)

For your Expected Value column, you can do something similar:

= CALCULATE(2.85 * MAX(PT[Value]),
            ALLEXCEPT(PT, PT[Person]),
            PT[Category] = 1,
            PT[Week] = EARLIER(PT[Week]) - 1)

(The EARLIER function gives you the value for the row you are in. The name refers to the earlier row context.)