Solved: Re: Return highest value from a visual table

info_algoritmia · ‎08-21-2023

Hey there,

I need a measure that returns the highest value from the current visual table, take a look at the screenshot below

I have date and total sales in columns, I need the measure that could return the highest value on my table based on the filters applied, when filter changes I also want the measure to show the new "highest value" based on filters.

parry2k · ‎08-21-2023

@info_algoritmia I see, try this:

SWITCH ( TRUE (),
  ISINSCOPE ( DateTable[Date], MAXX ( ALLSELECTED ( DateTable[Date] ), [Sales] ),
  ISINSCOPE ( DateTable[Month], MAXX ( ALLSELECTED ( DateTable[Month] ), [Sales] ),
  <<else condition>>
)

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

parry2k · ‎08-21-2023

@info_algoritmia I see, try this:

SWITCH ( TRUE (),
  ISINSCOPE ( DateTable[Date], MAXX ( ALLSELECTED ( DateTable[Date] ), [Sales] ),
  ISINSCOPE ( DateTable[Month], MAXX ( ALLSELECTED ( DateTable[Month] ), [Sales] ),
  <<else condition>>
)

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

info_algoritmia · ‎08-22-2023

@parry2kdo you know any particular reason that this is working for weeks, year but not for months? It's not returning the max value for months

info_algoritmia · ‎08-21-2023

@parry2kTHANK YOU! THIS WORKED!

parry2k · ‎08-21-2023

@info_algoritmia not sure what you used in ALLSELECTED, it should ALLSELECTED ( DateTable )

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

info_algoritmia · ‎08-21-2023

@parry2k it works when date column is in the visual, but if I changed it to "Months", look, the "51700" is the highest sales value for "dates", not for "months", how can I update this and make it dinamyc? I'd want know to show the "230400" for February isntead

parry2k · ‎08-21-2023

@info_algoritmia try this measure:

Highest Value = MAXX ( ALLSELECTED ( YourTable ), [Sales Measure] )

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

info_algoritmia · ‎08-21-2023

hey @parry2k it worked but I need the value to be dynamic, I utilized the "calendar table" in the ALL SELECTED, and it works for dates, but when I changed the dates for months it's still showing the max per date instead of month, how could I modify this automatically? I want the max value for the current filters and columns I have in the table visual, not only for dates (daily)