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
Lefuneste57
Helper I
Helper I

How to create a segment with N first line from a table report order bye amount value ?

Capture d'écran 2024-05-17 150629.pngHi, i have make a report with this data for exemple,i have many segment filter in my page.

So i would know how can i make a segment filter to choose the number of  line (order by amount) to display in my report.

I would have the same fonction as in filter pane from Power BI but like a segment.

 

So in filter Pane is like this :

Capture d'écran 2024-05-17 144427.png

 

 

 

 

 

And i would a filter segment in my page like this :

Capture d'écran 2024-05-17 144940.png

 

 

I have try with RankX but when i apply other filter it's not match.

I think, the best way is to insert a row with a measure in my display report, who give me a line number order by amount value like this

Capture d'écran 2024-05-17 150948.png

 

 

and so i can make my slicer filter with this row ..

 

Thanks for helping.

1 ACCEPTED SOLUTION
mark_endicott
Super User
Super User

@Lefuneste57 - Just to clarify, my solution will work for you & it is dynamic (because it uses a measure), so the ranking will be re-calculated according to any filters you use on the page. 

 

But you will need to use the measure below in any visual that you want to see this behaviour. There is no need to use a page filter because the measure handles the visual filtering. So if you want to filter an entire page, place the measure in every visual. 

 

It works by only displaying the amount value when the rank number is lower than or equal to the number in the slicer. The slicer is created using these instructions: https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-what-if

 

 

VAR Number_to_Rank = [Number of Lines (by order amount) Value] // from the numeric range parameter
VAR _amount = [_amount] // sum( amount )
RETURN
    IF (
        RANKX ( ALLSELECTED ( 'Table (6)'[Item], 'Table (6)'[Build] ), [_amount] ) <= Number_to_Rank,
        _amount
    ) // Returns the amount for any items that with a rank within the numbers specified in the Numeric range parameter. 

 

 

 

mark_endicott_0-1716280092091.pngmark_endicott_1-1716280121163.png

 

View solution in original post

13 REPLIES 13
mark_endicott
Super User
Super User

@Lefuneste57 - Great! I'm so glad it's worked for you. Anything in a measure is Dynamic, so when you need things to change according to filters, you need a measure. 

mark_endicott
Super User
Super User

@Lefuneste57 - Just to clarify, my solution will work for you & it is dynamic (because it uses a measure), so the ranking will be re-calculated according to any filters you use on the page. 

 

But you will need to use the measure below in any visual that you want to see this behaviour. There is no need to use a page filter because the measure handles the visual filtering. So if you want to filter an entire page, place the measure in every visual. 

 

It works by only displaying the amount value when the rank number is lower than or equal to the number in the slicer. The slicer is created using these instructions: https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-what-if

 

 

VAR Number_to_Rank = [Number of Lines (by order amount) Value] // from the numeric range parameter
VAR _amount = [_amount] // sum( amount )
RETURN
    IF (
        RANKX ( ALLSELECTED ( 'Table (6)'[Item], 'Table (6)'[Build] ), [_amount] ) <= Number_to_Rank,
        _amount
    ) // Returns the amount for any items that with a rank within the numbers specified in the Numeric range parameter. 

 

 

 

mark_endicott_0-1716280092091.pngmark_endicott_1-1716280121163.png

 

I just followed your instructions and it works fine! I learned something again 🙂 thanks to you very much. I didn't know about dynamic filters. Thanks again for your help, it’s GREAT.
Have a good day.

Can you send me the Pbx of your example please ?

I will try to follow the instructions in your link and create the measurement then integrate it into your formula. I'll keep you informed.
In any case, thank you for your valuable help.

v-xuxinyi-msft
Community Support
Community Support

Hi @Lefuneste57 

 

If I understand correctly, you need to create the number order by amount value column and then use it as slicer.

 

According to my tests, measure does not work as a slicer. You can create a calculated column, the following steps are for your reference:

 

Create a calculated column as follows

number order by amount value = RANKX('Table', [amount], , DESC, Dense)

 

Output:

vxuxinyimsft_1-1716172418567.png

 

Best Regards,
Yulia Xu

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thank for your reply.

I hope you will find a solution for my problem.

Créate a column is fine but the the problem is that in my report, I have filters on this visual. And when I filter, I lose sorting by amount.

 Capture d'écran 2024-05-20 125813.png

 

 

 

 

 

 

 

thanks again for trying to help me

@Lefuneste57 - this would not be an issue in my solution becase the ranking would be dynamic. 

 

The ranking that uses a column is static and therefore does not pay attention to filters. 

Thank you for answer,

It's still a shame that it is not possible to do this type of filter on a page knowing that the filter pane allows it (advanced "N first" filtering).

This solution is not optimal, especially if the model is very large. 

 

I have suggested using a Measure and a What-If-Parameter (Numeric Range Parameter), because it is optimal and best practice. The measure can be used as a Visual Level filter, the slicer uses the parameter value to control the number of lines the measure shows. 

mark_endicott
Super User
Super User

@Lefuneste57 - You can do this with RANKX, and you will also need to create numeric range parameter. 

 

Once you have created one of them, you can use the measure it creates as part of another measure that dynamically shows the amount, for the number of items you select. Below is my final measure, I will explain underneath:

 

VAR Number_to_Rank = [Number of Lines (by order amount) Value]
VAR _amount = [_amount]
RETURN
    IF (
        RANKX ( ALLSELECTED ( 'Table (6)'[Item], 'Table (6)'[Build] ), [_amount] ) <= Number_to_Rank,
        _amount
    )

 

The measure in Number_to_rank is from the numeric range parameter and presents whatever number is selected in the parameter. 

The _amount measure is a sum of Amount

The RANKX gives the ranking by _amount for the two colomns in your table, which are inside the ALLSELECTED. 

and the IF wrapper shows the amount for any rank that is less than or equal to the number from the Number_to_rank variable. 

 

mark_endicott_0-1715964469349.pngmark_endicott_1-1715964491093.pngmark_endicott_2-1715964506644.png

Please mark this as the solution if it works for you!

Hi,

Thanks for your answer but

I don't have the [Number of Lines (by order)] column in my report table. I added it in my example to show that i needed to sort by amount but I have to create this column and I don't know how.
 
Thanks
 

@Lefuneste57 - Sorry, I thought you may already be able to create a Numeric Range Parameter for the first part of my solution. 

 

Here are the instructions, https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-what-if, once you have followed these, you will get a measure in the new parameter table that you can reference in your Dynamic Rank measure.

 

In my solutuion, the measure that is auto generated by the parameter is 

[Number of Lines (by order amount) Value]

because I have named the parameter "Number of Lines (by order amount)"

 

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.

Top Solution Authors