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 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... 

  • 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?

2 Replies

  • TuqueLogic's avatar
    TuqueLogic
    Icon for Helper I rankHelper I
    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.