topic Re: Calculation using Multiple Values for Maximum Row in DAX Commands and Tips

Calculation using Multiple Values for Maximum Row

thomas_pike — Thu, 28 Jul 2022 03:46:23 GMT

Good Afternoon,

I am trying to create the following table using DAX:

ID	Index	Value	Pitch	Result
1	1	2	2	2
1	2	4	2	4
1	3	6	2	6
2	1	5	3	3
2	2	7	3	6
2	3	9	3	9

Result is a calculated column with the following formula when grouped by ID:

Pitch = Value / Index <- for the row with the max value per ID.

Result = Index * Pitch

What is the best way to calculate the Pitch column?

Thank you for your assistance

Re: Calculation using Multiple Values for Maximum Row

tamerj1 — Thu, 28 Jul 2022 06:36:32 GMT

Hi thomas_pike
Please do the following https://we.tl/t-JYIy6UCpSM

Pitch = VAR CurrentIDTable = CALCULATETABLE ( Data, ALLEXCEPT ( Data, Data[ID] ) ) VAR LastIndexRecord = TOPN ( 1, CurrentIDTable, Data[Index] ) VAR LastIndex = MAXX ( LastIndexRecord, Data[Index] ) VAR LastValue = MAXX ( LastIndexRecord, Data[Value] ) RETURN LastValue / LastIndex

Result = Data[Index] * Data[Pitch]

Re: Calculation using Multiple Values for Maximum Row

thomas_pike — Thu, 28 Jul 2022 07:11:20 GMT

Thanks tamerj1 that got me on the right path.