cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

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

Hi, I am a newbie to Power BI.

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

 

 dada.PNG

=MAX(IF(A$2:A$41 = A2, IF(C$2:C$41 =C2, B$2:B$41)))

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. Any suggestions/corrections, please?

 

Weekly Maximum = Max(if(PT[Person]= PT[Person],IF(PT[Weeks]=PT[Weeks]),PT[value]))

 sni.PNG

And also to write a formula for taking data from two different tables. 

 

 

TIA Smiley Happy 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User III
Super User III

Re: How to find the maximum value from a column satisfying two or more IF conditions.

HI @vishnuprashanth

 

Try this

 

Max Value =
CALCULATE ( MAX ( PT[Value] ), ALLEXCEPT ( PT, PT[Person], PT[Weeks] ) )
Try my new Power BI game Cross the River

View solution in original post

10 REPLIES 10
Highlighted
Super User III
Super User III

Re: How to find the maximum value from a column satisfying two or more IF conditions.

HI @vishnuprashanth

 

Try this

 

Max Value =
CALCULATE ( MAX ( PT[Value] ), ALLEXCEPT ( PT, PT[Person], PT[Weeks] ) )
Try my new Power BI game Cross the River

View solution in original post

Highlighted
Helper III
Helper III

Re: How to find the maximum value from a column satisfying two or more IF conditions.

@Zubair_Muhammad Thank you so much. Your solution worked.

 

Is it possible to have max(value) of the 2nd week in first, 3rd week in second and so? I need to visualize the progress in the value comparing to the previous week. 

Any suggestion would be highly valuable. Thank again 🙂 

Highlighted
Super User III
Super User III

Re: How to find the maximum value from a column satisfying two or more IF conditions.

It's very much possible. I am out of office now. Will get back to you after few hours
Try my new Power BI game Cross the River
Highlighted
Helper III
Helper III

Re: How to find the maximum value from a column satisfying two or more IF conditions.

Thanks, @Zubair_Muhammad I will also try to find the solution in the meantime. 

Highlighted
Helper III
Helper III

Re: How to find the maximum value from a column satisfying two or more IF conditions.

Hi @Zubair_Muhammad I am trying to create the expected value from my dataset below. Capture.PNG


I am trying to calculate the Max Value by Category using the formula below,

Weekly Maximum = 
CALCULATE ( MAX ( PT[total energy (J/kg)] ), ALLEXCEPT ( PT, PT[athlete], PT[WeekSequence] , PT[session category] = "MATCH") )

But was throwing me an error.

 

err.PNG 

 

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. 

Can I do all this in one new column, or should I add another column to add the expected weekly values? 

Highlighted
Super User III
Super User III

Re: How to find the maximum value from a column satisfying two or more IF conditions.

@vishnuprashanth

 

Try this

 

Weekly Maximum =
CALCULATE (
    MAX ( PT[total energy (J/kg)] ),
    FILTER (
        ALLEXCEPT ( PT, PT[athlete], PT[WeekSequence] ),
        PT[session category] = "MATCH"
    )
)
Try my new Power BI game Cross the River
Highlighted
Super User III
Super User III

Re: How to find the maximum value from a column satisfying two or more IF conditions.

@vishnuprashanth

 

To get next week max in current week row.

 

Try this calculated column

 

Max Value for Next Week =
CALCULATE (
    MAX ( PT[Value] ),
    FILTER ( ALLEXCEPT ( PT, PT[Person] ), PT[Weeks] = EARLIER ( PT[Weeks] ) + 1 )
)
Try my new Power BI game Cross the River
Highlighted
Super User III
Super User III

Re: How to find the maximum value from a column satisfying two or more IF conditions.

@vishnuprashanth

 

If you need a MEASURE then you can use this

 

Max Value for Next Week =
CALCULATE (
    MAX ( PT[Value] ),
    FILTER (
        ALLEXCEPT ( PT, PT[Person] ),
        PT[Weeks]
            = SELECTEDVALUE ( PT[Weeks] ) + 1
    )
)
Try my new Power BI game Cross the River
Highlighted
Helper III
Helper III

Re: How to find the maximum value from a column satisfying two or more IF conditions.

Thank you so much @Zubair_Muhammad. This worked perfectly. Thanks a lot.


Can you kindly suggest me some good resource for Learning DAX and Power query, please!! Much needed for my work now.

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors