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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
marcura
Helper II
Helper II

Return the last 5 by double filters

Hello community! 

I need to return only the last 5 rows, by QTDE and DATA_PEDIDO.

ITEMDATA_PEDIDOQTDE
101/01/202050
102/01/202020
202/02/202030
302/02/202020
402/02/2020100
402/02/202060
402/02/2020120
402/02/202080
402/02/202090
402/02/202050
402/02/202020
403/02/202010


For example, if the user filter ITEM = 4:

402/02/2020100
402/02/202060
402/02/2020120
402/02/202080
402/02/202090
402/02/202050
402/02/202020
403/02/202010


I need to return only 5 rows. (using Order_Date and QTY as value for rank)

402/02/2020100
402/02/2020120
402/02/202080
402/02/202090
403/02/202010


Any suggestions? I've tried DAX RANK in calculated column, wihtout success.

 

Thanks.

 

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

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:

8.21.3.1.png

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

View solution in original post

9 REPLIES 9
v-eqin-msft
Community Support
Community Support

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:

8.21.3.1.png

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

harshnathani
Community Champion
Community Champion

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

 

 

1.jpg

 

 

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

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

 

1.jpg

 

 

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!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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