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

[Lefuneste57]

Hi, 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 :

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

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

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

Thanks for helping.

[mark_endicott]

@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 · Accepted solution]

@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. 

[Lefuneste57]

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.

[Lefuneste57]

Can you send me the Pbx of your example please ?

[Lefuneste57]

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]

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:

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.

[Lefuneste57]

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.

 

thanks again for trying to help me

[mark_endicott]

@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. 

[Lefuneste57]

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

[mark_endicott]

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]

@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. 

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

[Lefuneste57]

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

[mark_endicott]

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