sort index

[netanel]

Hello everyone!
I'm trying to sort out the rows of my report.
I want to open a new index column that will work according to an existing column.

column B the new one, According to the data of column B.

Thanks!

[amitchandak]

@netanel , not very clear

refer

https://community.powerbi.com/t5/Desktop/Add-calculated-index-column-by-DAX/td-p/72448

https://stackoverflow.com/questions/45715963/creating-an-index-column-for-power-bi

https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-sort-by-column

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

[az38]

Hi @netanel 

for Index columns you can use 2 ways:

1. Add Index column in Power QUery mode

2. Add RANKX() columns. Complete guide to use use RANKX you can find here https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns

[netanel]

1. I know really well the problem is that he gives a different number to the same description.
2. It is excellent, but I have a different description that is not perceived in ranks.

So I'm still stuck...

A      1

A      1

A:     2

B      3

this is how its need to look like

[v-yingjl]

Hi @netanel ,

Is this your expected result?

If so, try to create a calculated column like this:

Column =
RANKX ( 'Table', 'Table'[Column A],, ASC, DENSE )

Best Regards,
Yingjie Li

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

[netanel]

Hey,

This formula is exactly what I use

But it brings me the next result

"A single value for column 'תיאור' in table 'Report (2)' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

I think it's because iam trying to create a measure instead of a calculated column.

still stuck...

[v-yingjl]

Hi @netanel ,

That's right. This fomula will only work with a calculated column.

If you want to create a measure, try this formula:

Measure = 
RANKX ( ALL('Table'), CALCULATE(MAX('Table'[Column A])),, ASC, DENSE )

But it will only show a single value in the table visual because table visual will not show repeated value of one column if there is not any other field in the table visual.

Best Regards,
Yingjie Li

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

[netanel]

Sorry I think I just did not explain myself correctly

I try to prepare a profit and loss statement, but the sections do not sort me out in order.

I tried to sort column A by column C in the table I made before but got the message in the picture

That's why I tried all the RANK formulas I tried to create a new and more correct order column
What am I doing wrong?

Thank you so much for your time!

[netanel]

Hey,

Column B My intention for a new index column
To be based on column A.
Column C is an index column that I have already built in Excel, it actually brings the number 1 to the first item in profit and loss and also to the first item in the balance sheet and other reports and this way the tab of the comment pops up.

[v-yingjl]

Hi @netanel ,

Seems I cannot reproduce it in my environment. Could you please consider sharting a dummy .pbix file for further discussion? Please remeber to replace the sensitive information in your file.

Best Regards,

Yingjie Li

[netanel]

Sorry I sent a blocked link.
That's a good link 

https://1drv.ms/u/s!AonyYI-TdspHadeIXyTKZfHG7IE?e=niP2Ch

Column Description Should be sort by Column line

or other solution.

Thanks!

[v-yingjl]

Hi @netanel ,

Please try to create a calculated column using the previous formula and use sort by column on column description based on the calculated column.

Or try to use the sort by description in the visual directly:

According to the previous error message, you can refer these simliar issues: 

1. Sorting error - can't be more than one value 
2. Sort by columns returns error 

Best Regards,
Yingjie Li

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

Best Regards,
Yingjie Li

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

[netanel]

Hi thanks for the response,
I think there's something else I'm missing here, this is the result I get

[v-yingjl · Accepted solution]

Hi @netanel ,

Difficult to explain this error message. One simple solution is that we had better go back to create measure😑. But sort by column function is only useful to columns, so a potential solution is that put the measure in the visual and click the three dots(...) on the right corner of this visual, use sort by in the visual by the measure or description as I previous mentioned.

Best Regards,
Yingjie Li

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

[v-yingjl]

Hi @netanel ,

In your previous post, it relates column A and B. If you want to use sort by functions based on column C and face this error message, what is the data type of these columns?

Best Regards,
Yingjie Li

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