topic DAX getting MIN value from rows with same value in different column in DAX Commands and Tips

DAX getting MIN value from rows with same value in different column

TuqueLogic — Mon, 10 Feb 2025 20:21:45 GMT

Feel silly asking as this seems like it should be simple.

Looking to create a new column for a table that contains the Min 'Start Date' value for rows that have the same InitialResultID.
IE just dealing with one table and two cols within it.

Things that I've tried:
CALCULATE(MIN(table[Start Date]), FILTER(table, TestBillingAgentTasks[InitialResultID])
CALCULATE(MIN(table[Start Date]), FILTER(table, TestBillingAgentTasks[InitialResultID]=table[InitialResultID]))

More, how do you effectively reference a current row value instead of using scalar for comparative expressions like what is used in the filter fuction?
I find this easier to do / find in the manual for all of Excel, Pandas, SQL...

Re: DAX getting MIN value from rows with same value in different column

TuqueLogic — Mon, 10 Feb 2025 20:44:52 GMT

EARLIER seems to do it:
CALCULATE(MIN(table[StartDate]), FILTER(table, TestBillingAgentTasks[InitialResultID]=EARLIER(table[InitialResultID])))

Here it states EARLIER refersto the previous 'table scan':
EARLIER - DAX Guide

Can someone point me to where I can understand better what table scans are, where in the code they are initiated, and by which types of functions?

Re: DAX getting MIN value from rows with same value in different column

Anonymous — Tue, 11 Feb 2025 03:00:27 GMT

Hi TuqueLogic ,

A table scan examines each record in a table row by row to determine if they meet the specified conditions. There are two types of functions that trigger a table scan: filtering functions, such as FILTER, and iteration functions, such as SUMX. The initiation of a table scan is related to these functions, occurring when these functions are called and during the calculation of row context.

I also created simple example data.

TestBillingAgentTasks

table

Create two calculated columns, the former is used to get the same ID and the latter calculates the minimum date:

SameValue = LOOKUPVALUE('TestBillingAgentTasks'[InitialResultID],'TestBillingAgentTasks'[InitialResultID],'table'[InitialResultID])

MinValue = IF('table'[SameValue]<>0,CALCULATE(MIN('table'[Start Date]),FILTER('table','table'[SameValue]=EARLIER('table'[InitialResultID]))))

Result:

If there is a relationship between the two tables, the following formula can also be used:

MinDate = CALCULATE(MIN('table (2)'[Start Date]),FILTER(ALLEXCEPT('table (2)','table (2)'[InitialResultID]),'table (2)'[InitialResultID]=RELATED('TestBillingAgentTasks（2）'[InitialResultID])))

Best Regards,
Zhu

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.