Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

help with querying a column in a variable

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

 

 

 

Need Help
Tips and Tricks
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    4 years ago

    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.

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago
    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's avatar
      amitchandak
      Super User
      4 years ago

      Anonymous , Try like

       

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        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.

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Community Support
    4 years ago

    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.