Forum Discussion

TuqueLogic's avatar
TuqueLogic
Icon for Helper I rankHelper I
1 year ago
Solved

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

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 ...
  • TuqueLogic's avatar
    TuqueLogic
    1 year ago

    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?

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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.