Forum Discussion
Dynamic ranking in date table
Hello everyone
I have a date table with two columns, the record id and the date. There are no duplicate records. I want to create a new date ranking column, ordered from lowest to highest, that always starts with 1 and works in any filtering option. In the following image I paste an image of the complete table and another of the filtered table for a certain date range, the ranking obtained and the desired one.
Thank you very much for your collaboration.
14 Replies
Syndicate_Admin Try:
Rank Measure Ascending = RANKX(ALLSELECTED('Table'),CALCULATE(MAX([fechas])),,ASC,Skip) Rank Measure Descending = RANKX(ALLSELECTED('Table'),CALCULATE(MAX([fechas])),,DESC,Skip)Thank you very much for the reply. What you propose gives error by circular dependency "A circular dependency was detected: CALEN[Rank Measure Ascending]."
Syndicate_Admin Are you creating it as a Measure or a Column? A Column won't work because it won't be dynamic. Sample PBIX is attached below signature.
It's a new column. Is it not possible to include the id column in the solution? If you subtract the minimum id from the ranking once filtered and add 1 it gives you. Is that possibility?
Thanks a lot
Syndicate_Admin Columns are only calculated at the time of data refresh. So, if you are going to have this work where the user can change things like slicers or filtering, etc. then a column will not work because it won't recalculate based on the filtering.
If however you are intending this to work where you can create new tables in your model and specify the filtering options then you can simply use a simple ADDCOLUMNS and a simple RANKX formula RANKX('Table',[Date]).
Thank you very much for the help, but the proposed approach gives error "The expression refers to several columns. You cannot convert multiple columns to a scalar value"
