Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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...
Solved! Go to 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.
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 , Try like
Var table3 =
ADDCOLUMNS(
table2,
"@Second Order",
calculate(
minX (table2, [@second order helper],
ALLEXCEPT( ECOMMERCE_REPLACEMENTS_AGG,ECOMMERCE_REPLACEMENTS_AGG[userid]
)
)
)
)
in other words, the ALLEXCEPT doesn't compute
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.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
87 | |
87 | |
85 | |
67 | |
49 |
User | Count |
---|---|
135 | |
112 | |
100 | |
66 | |
62 |