Max scalar value from first 2 rows

[Anonymous]

Hello smart people,

I want to get the highest value (cycle time days) from the first two rows.

DAX that i tried:

CycleTime_MaxValueFirstTwoRows = CALCULATE('Test view'[CycleTime_MaxDays],TOPN(2,'Test view',MAX('Test view'[Cycle Time Days])))

The output now is the highest value of the whole table. It should be the highest of one of first two rows, just one scalar value. 

Please help me,

Kind regards,

Daan

[v-jincheng-msft]

Hi @Anonymous ,

Please try the DAX as followed:

CycleTime_MaxValueFirstTwoRows = CALCULATE(MAX('Test view'[CycleTime_MaxDays]),TOPN(2,'Test view'))

Hope it helps!

Best regards,
Community Support Team_ Joseph Ji

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[123abc]

You can use the following DAX expression:

CycleTime_MaxValueFirstTwoRows =

CALCULATE(

     MAX('Test view'[Cycle Time Days]),

     TOPN(2, 'Test view', 'Test view'[CycleTime_MaxDays], DESC)

)

Here's a breakdown of the changes:

1. I replaced 'Test view'[CycleTime_MaxDays] with 'Test view'[Cycle Time Days] inside the MAX function because you want to find the maximum value for the 'Cycle Time Days' column.

2. I added DESC to the TOPN function to sort the rows in descending order based on the 'Cycle Time Days' column. This ensures that the top two rows will have the highest values.

With these adjustments, the expression should give you the maximum value of the 'Cycle Time Days' column for the first two rows in your 'Test view' table.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[Anonymous]

Thank you for your reply.

It seems like it gives the same output like MAX('Test view'[Cycle Time Days])

The same problem as before.

[123abc]

I see, thank you for providing feedback. Let's try a different approach to get the maximum value for the first two rows. You can use the FILTER function to filter the table and then calculate the maximum. Here's an alternative DAX formula:

CycleTime_MaxValueFirstTwoRows =
CALCULATE(
MAX('Test view'[CycleTime_MaxDays]),
FILTER(
TOPN(2, 'Test view', 'Test view'[YourSortingColumn]),
'Test view'[CycleTime_MaxDays] = MAX('Test view'[CycleTime_MaxDays])
)
)

In this formula:

1. Replace 'CycleTime_MaxDays' with the actual column name for which you want to find the maximum value.
2. Replace 'YourSortingColumn' with the column you want to use for sorting the top two rows. If you want to use the default sorting order, you can replace it with the actual column name you are interested in.

This formula first uses TOPN to get the top two rows based on the specified sorting column and then filters those rows where 'CycleTime_MaxDays' is equal to the maximum value of 'CycleTime_MaxDays' in the entire table. Finally, it calculates the maximum value within this filtered subset.

Give this formula a try and see if it provides the desired result.