Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello community!
I need to return only the last 5 rows, by QTDE and DATA_PEDIDO.
ITEM | DATA_PEDIDO | QTDE |
1 | 01/01/2020 | 50 |
1 | 02/01/2020 | 20 |
2 | 02/02/2020 | 30 |
3 | 02/02/2020 | 20 |
4 | 02/02/2020 | 100 |
4 | 02/02/2020 | 60 |
4 | 02/02/2020 | 120 |
4 | 02/02/2020 | 80 |
4 | 02/02/2020 | 90 |
4 | 02/02/2020 | 50 |
4 | 02/02/2020 | 20 |
4 | 03/02/2020 | 10 |
For example, if the user filter ITEM = 4:
4 | 02/02/2020 | 100 |
4 | 02/02/2020 | 60 |
4 | 02/02/2020 | 120 |
4 | 02/02/2020 | 80 |
4 | 02/02/2020 | 90 |
4 | 02/02/2020 | 50 |
4 | 02/02/2020 | 20 |
4 | 03/02/2020 | 10 |
I need to return only 5 rows. (using Order_Date and QTY as value for rank)
4 | 02/02/2020 | 100 |
4 | 02/02/2020 | 120 |
4 | 02/02/2020 | 80 |
4 | 02/02/2020 | 90 |
4 | 03/02/2020 | 10 |
Any suggestions? I've tried DAX RANK in calculated column, wihtout success.
Thanks.
Solved! Go to Solution.
Hello @marcura ,
In my opinion, you want to select top5 data based on columns DATA_PEDIOD and QTDE, right?
You can use the following formula:
rankColumn =
VAR a = [ITEM]
VAR b = [DATA_PEDIDO]
VAR t1 =
FILTER ( ALL ( 'Last5' ), Last5[ITEM] = a )
VAR t2 =
FILTER ( ALL ( 'Last5' ), Last5[ITEM] = a && 'Last5'[DATA_PEDIDO] = b )
RETURN
RANKX (
t1,
RANKX ( t1, [DATA_PEDIDO],, DESC, SKIP ) * 100
+ RANKX ( t2, [QTDE],, DESC, SKIP ) * 10,
,
ASC,
SKIP
)
My visualization looks like this:
Is the result you want? If you have any questions, upload some data samples and the expected output.
Please mask sensitive data before uploading.
Best regards
Eyelyn Qin
Hello @marcura ,
In my opinion, you want to select top5 data based on columns DATA_PEDIOD and QTDE, right?
You can use the following formula:
rankColumn =
VAR a = [ITEM]
VAR b = [DATA_PEDIDO]
VAR t1 =
FILTER ( ALL ( 'Last5' ), Last5[ITEM] = a )
VAR t2 =
FILTER ( ALL ( 'Last5' ), Last5[ITEM] = a && 'Last5'[DATA_PEDIDO] = b )
RETURN
RANKX (
t1,
RANKX ( t1, [DATA_PEDIDO],, DESC, SKIP ) * 100
+ RANKX ( t2, [QTDE],, DESC, SKIP ) * 10,
,
ASC,
SKIP
)
My visualization looks like this:
Is the result you want? If you have any questions, upload some data samples and the expected output.
Please mask sensitive data before uploading.
Best regards
Eyelyn Qin
Hello, can I make the top5 data based on columns DATA_PEDIDO, QTDE and DATA_RECEBIMENTO?
I've tried this:
=
VAR a = vw_fato_pedido_compra[item_id]
VAR b = vw_fato_pedido_compra[Data do Pedido format]
VAR c = vw_fato_pedido_compra[Data do Recebimento format]
VAR t1 =
FILTER ( ALL (vw_fato_pedido_compra), vw_fato_pedido_compra[item_id] = a )
VAR t2 =
FILTER ( ALL ( vw_fato_pedido_compra ), vw_fato_pedido_compra[item_id] = a && vw_fato_pedido_compra[Data do Pedido format]= b )
VAR t3 =
FILTER ( ALL ( vw_fato_pedido_compra ), vw_fato_pedido_compra[item_id] = a
&& vw_fato_pedido_compra[Data do Pedido format]= b
&& vw_fato_pedido_compra[Data do Recebimento format] = c )
RETURN
RANKX (
t1,
RANKX ( t1, [Data do Pedido format],, DESC, SKIP ) * 1000
+ RANKX ( t2, [pedido_compra_id],, DESC, SKIP ) * 100
+ RANKX ( t3, [pedido_compra_id],, DESC, SKIP ) * 10,
,
ASC,
SKIP
)
But doesn't work, the calculated column for this is equal to your answer..
Can you help me ?
Thank you, worked!!
Hi @marcura ,
Try this measure
Ranking Measure = RANKX(FILTER(ALLSELECTED('Table'),'Table'[ITEM] = MAX('Table'[ITEM])), CALCULATE(SUMX('Table',YEAR('Table'[DATA_PEDIDO])*100000 + MONTH('Table'[DATA_PEDIDO])*10000 + Day('Table'[DATA_PEDIDO]) + 'Table'[QTDE])))
Post this add this as a visual filter.
Regards,
Harsh Nathani
Thanks for the measure, Unfortunately, the loading time for the table has increased so much, I've used, this calculated column:
rankColumn = VAR a = [ITEM] VAR b = [DATA_PEDIDO] VAR t1 = FILTER ( 'Last5'), Last5[ITEM] = a ) VAR t2 = FILTER ( 'Last5'), Last5[ITEM] = a && 'Last5'[DATA_PEDIDO] = b ) RETURN RANKX ( t1, RANKX ( t1, [DATA_PEDIDO],, DESC, SKIP ) * 100 + RANKX ( t2, [QTDE],, DESC, SKIP ) * 10, , ASC, SKIP ) )
@marcura ,Can you share sample data and sample output in table format?
I think Rank can help
For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/3...
I've tried to explain better, can u take a look? Thanks.
Hi @marcura ,
Not very clear.
Can you share a larger resolution picture too or the sample data in text format.
Alternatively, you can try Visual Filters
Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)
I've tried to explain better...Can you take a look? thank you!
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
120 | |
101 | |
71 | |
61 |