Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
vishnuprashanth
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
Zubair_Muhammad
Community Champion
Community Champion

HI @vishnuprashanth

 

Try this

 

Max Value =
CALCULATE ( MAX ( PT[Value] ), ALLEXCEPT ( PT, PT[Person], PT[Weeks] ) )

Regards
Zubair

Please try my custom visuals

View solution in original post

10 REPLIES 10
Zubair_Muhammad
Community Champion
Community Champion

HI @vishnuprashanth

 

Try this

 

Max Value =
CALCULATE ( MAX ( PT[Value] ), ALLEXCEPT ( PT, PT[Person], PT[Weeks] ) )

Regards
Zubair

Please try my custom visuals

@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 🙂 

It's very much possible. I am out of office now. Will get back to you after few hours

Regards
Zubair

Please try my custom visuals

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

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? 

@vishnuprashanth

 

Try this

 

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

Regards
Zubair

Please try my custom visuals

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.

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

Regards
Zubair

Please try my custom visuals

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

Regards
Zubair

Please try my custom visuals

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.