help with querying a column in a variable

[Anonymous]

Hello everybody,

I need a little help with the following:

1. Help in optimizing the code

2. Help in understanding why I can't query [@Second order helper] in table 3.

3. Tips and tricks in working with variables - I am adding a new variable for every new [@new column], otherwise I cannot use it in a query (i.e. in table1 I added [@First Order], but in order to add a column that is dependant of it, I created a new var table2 to create [@Second order helper]. Is there another way of doing this? I feel this is not very productive...

[v-yanjiang-msft · Accepted solution]

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.

[Anonymous]

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] )

),

"@First Order",

calculate(

min (ECOMMERCE_REPLACEMENTS_AGG[Data Plasare] ),

ALLEXCEPT ( ECOMMERCE_REPLACEMENTS_AGG,ECOMMERCE_REPLACEMENTS_AGG[userid] )

)

)

var table2 =

ADDCOLUMNS(

table1,

"@Second Order helper",

if(

ECOMMERCE_REPLACEMENTS_AGG[Data Plasare] > [@First Order],

ECOMMERCE_REPLACEMENTS_AGG[Data plasare]

)

)

Var table3 =

ADDCOLUMNS(

table2,

"@Second Order",

calculate(

min ( [@second order helper],

ALLEXCEPT( ECOMMERCE_REPLACEMENTS_AGG,ECOMMERCE_REPLACEMENTS_AGG[userid]

)

)

)

)

return

table3

[amitchandak]

@Anonymous , Try like

Var table3 =
ADDCOLUMNS(
table2,
"@Second Order",
calculate(
minX (table2, [@second order helper],
ALLEXCEPT( ECOMMERCE_REPLACEMENTS_AGG,ECOMMERCE_REPLACEMENTS_AGG[userid]
)
)
)
)

[Anonymous]

in other words, the ALLEXCEPT doesn't compute

[Anonymous]

Thanks for the quick reply. Using minx enables me to use [@second order helper], but the calculation doesn't compute the MIN of [@second order helper] per user. Instead, it returns the same one value everywhere.