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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.