help with querying a column in a variable

Hi Anonymous ,

According to your description, I create a sample.

Your expected output is create a new table including above two columns and column “@First Order” which is the minimum value of Data Plasare, column “@Second Order” which is the next smallest value of Data Plasare, right?

Here's my solution, below is the new table formula, I add a column Rank, which is very convenient if you want to calculate the third order, fourth order etc.

Avg no of months to second repl TBL = 
VAR Table1 =
    ADDCOLUMNS (
        FILTER (
            SUMMARIZE (
                ECOMMERCE_REPLACEMENTS_AGG,
                ECOMMERCE_REPLACEMENTS_AGG[userid],
                ECOMMERCE_REPLACEMENTS_AGG[Data Plasare]
            ),
            NOT ISBLANK ( ECOMMERCE_REPLACEMENTS_AGG[userid] )
        ),
        "Rank",
            RANKX (
                FILTER (
                    ALL ( 'ECOMMERCE_REPLACEMENTS_AGG' ),
                    'ECOMMERCE_REPLACEMENTS_AGG'[userid]
                        = EARLIER ( 'ECOMMERCE_REPLACEMENTS_AGG'[userid] )
                ),
                'ECOMMERCE_REPLACEMENTS_AGG'[Data Plasare],
                ,
                ASC,
                DENSE
            ),
        "@First Order",
            CALCULATE (
                MIN ( ECOMMERCE_REPLACEMENTS_AGG[Data Plasare] ),
                ALLEXCEPT ( ECOMMERCE_REPLACEMENTS_AGG, ECOMMERCE_REPLACEMENTS_AGG[userid] )
            )
    )
VAR table2 =
    ADDCOLUMNS (
        table1,
        "@Second Order",
            MINX (
                FILTER (
                    Table1,
                    [Rank] = 2
                        && [userid] = EARLIER ( 'ECOMMERCE_REPLACEMENTS_AGG'[userid] )
                ),
                [Data Plasare]
            )
    )
RETURN
    table2

Get the result.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

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