topic Re: Urgent help on DAX - MAX index in DAX Commands and Tips

Urgent help on DAX - MAX index

Newbie12345 — Sun, 07 Apr 2024 08:42:54 GMT

Dear Power BI Pros,

I need your urgent help on finding the max value.

I want to get the index of max KG.
KG is my measure.

Thank you!

Re: Urgent help on DAX - MAX index

AnalyticsWizard — Sun, 07 Apr 2024 08:50:23 GMT

To find the index of the maximum value of a measure in Power BI, you can use the DAX function MAXX along with FILTER to iterate through the table and find the row with the highest ‘KG’ value. Here’s an example of how you might write this measure:

MaxKGIndex = 
VAR MaxKGValue = MAXX(ALL(Table1), [KG])
RETURN
CALCULATE(
    MIN(Table1[Index]),
    FILTER(
        ALL(Table1),
        [KG] = MaxKGValue
    )
)

This measure calculates the maximum ‘KG’ value across all rows in ‘Table1’ and then finds the corresponding ‘Index’ where this maximum value occurs. The ALL function is used to remove any filters that might limit the rows being evaluated. Remember to replace ‘Table1’ with the actual name of your table.

Please note that if there are multiple rows with the same maximum ‘KG’ value, this measure will return the smallest index among them. If you need further assistance or have more specific requirements, feel free to ask! 😊

Re: Urgent help on DAX - MAX index

Newbie12345 — Sun, 07 Apr 2024 10:14:42 GMT

Dear AnalyticsWizard
Thank you so much for your response.
I tried it, unfortunately it is saying 'Visual is exceeded the available resourses' even after selecting single category.
Could you please help around with this?

Re: Urgent help on DAX - MAX index

Dangar332 — Sun, 07 Apr 2024 11:15:45 GMT

Hi, Newbie12345

You can refer below link for error solving LINK

Re: Urgent help on DAX - MAX index

tamerj1 — Sun, 07 Apr 2024 21:33:48 GMT

Hi Newbie12345

please try

Expected Output =
VAR T =
GENERATE (
VALUES ( 'Table'[Category] ),
CALCULATETABLE (
TOPN ( 1, SUMMARIZE ( 'Table', 'Table'[Kg], 'Table'[Index] ), 'Table'[Kg] ),
ALLEXCEPT ( 'Table', 'Table'[Category] )
)
)
RETURN
MAXX ( T, 'Table'[Index] )

Re: Urgent help on DAX - MAX index

Newbie12345 — Mon, 08 Apr 2024 05:27:07 GMT

Dear tamerj1 ,

Thank you so much for your response!
KG is not a column, it is a measure, could pls you suggest me how to pass KG measure inside calculatetable.

Re: Urgent help on DAX - MAX index

tamerj1 — Mon, 08 Apr 2024 12:57:41 GMT

Newbie12345

Please try

Expected Output =
VAR T =
GENERATE (
VALUES ( 'Table'[Category] ),
CALCULATETABLE (
TOPN ( 1, SUMMARIZE ( 'Table', 'Table'[Index] ), [Kg] ),
ALL ( 'Table'[Index] )
)
)
RETURN
MAXX ( T, 'Table'[Index] )

Re: Urgent help on DAX - MAX index

Newbie12345 — Tue, 09 Apr 2024 11:21:47 GMT

Dear tamerj1 ,

Thank you so much, I will try this.

Re: Urgent help on DAX - MAX index

Newbie12345 — Tue, 09 Apr 2024 11:22:46 GMT

Dear Dangar332 ,

Thank you so much!