Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Anonymous
Not applicable

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

 

Capture.JPG

 

 

1 ACCEPTED SOLUTION
v-yanjiang-msft
Community Support
Community Support

Hi @Anonymous ,

According to your description, I create a sample.

vkalyjmsft_0-1642671304657.png

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.

vkalyjmsft_1-1642672034347.png

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.

View solution in original post

5 REPLIES 5
v-yanjiang-msft
Community Support
Community Support

Hi @Anonymous ,

According to your description, I create a sample.

vkalyjmsft_0-1642671304657.png

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.

vkalyjmsft_1-1642672034347.png

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
Not applicable

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

@Anonymous , Try like

 

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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here
Anonymous
Not applicable

in other words, the ALLEXCEPT doesn't compute

Anonymous
Not applicable

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.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.