topic Re: How to create a segment with N first line from a table report order bye amount value ? in DAX Commands and Tips

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

Lefuneste57 — Fri, 17 May 2024 13:19:29 GMT

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.

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

mark_endicott — Fri, 17 May 2024 16:48:54 GMT

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!

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

Lefuneste57 — Fri, 17 May 2024 18:58:50 GMT

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

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

Anonymous — Mon, 20 May 2024 02:45:31 GMT

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.

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

mark_endicott — Mon, 20 May 2024 09:30:50 GMT

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

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

mark_endicott — Mon, 20 May 2024 09:30:05 GMT

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.

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

Lefuneste57 — Mon, 20 May 2024 11:01:18 GMT

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

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

mark_endicott — Mon, 20 May 2024 11:05:36 GMT

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.

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

Lefuneste57 — Tue, 21 May 2024 06:47:23 GMT

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

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

mark_endicott — Tue, 21 May 2024 08:30:51 GMT

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.

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

Lefuneste57 — Tue, 21 May 2024 09:08:51 GMT

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.

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

Lefuneste57 — Tue, 21 May 2024 10:35:15 GMT

Can you send me the Pbx of your example please ?

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

Lefuneste57 — Tue, 21 May 2024 10:56:31 GMT

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.

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

mark_endicott — Tue, 21 May 2024 11:03:35 GMT

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.