I would like to display the index (1, 2, 3, 4) that would show the rank number each of my records

[E12345]

Hi!

I have a table with two columns: "Presenter", "Presented Date".

I would really like to be able to "rank" the presentations in order by "Presented Date" in askending order so all records are shown as 1, 2, 3, etc. 
I was able to create a column that uses a DAX function to display the record order (as well as just add an index column using Power Query to do the same)

The issue is that I also have a slicer which lets me select presentations by "Presenter" (Person). With the slicer used, the order of presentations gets messed up, and the numbers do not display in order. How can I dynamically keep consequitive numbers no matter what is on my final list? Is it even possible? I am assuming I need a Dax Measure for that, which calculates on the run, not a Column. 

Here is a screenshot of what I already have (numner 1) and #2 is what I need to change to show 1, 2, 3 (to dynamically recalculate). Thanks!

[E12345 · Accepted solution]

UPDATE: 
I got it, it finally worked! Below is the DAX function that worked for me! 😄

(Ashish Mathur, thank you anyway, I appreciate your input!)

Running Total =

CALCULATE(

    COUNTROWS('My table'),

    FILTER(

        ALLSELECTED('My table'),

        'My table'[Date Presented] <= MAX('My table'[Date Presented])

    )

)

[E12345]

s, the measure works fine, but when I use a slicer and select a "Presenter", the numbers show the actual original index. And I need them to recalculate and be in order. 
This is my measure:

Index (DAX Mesure) = RANKX(ALL('My Table'[Date Presented]),'My Table'[Date Presented],,ASC)

The Index is nice and ordered when the slicer is unot touched, like on thios screenshot:

However, if I use the slicer and select (for example) "Mary", the index for mary

s data shows 2, 7, 12, and I need it to be 1, 2, 3:

[Ashish_Mathur]

Hi,

What happens when you use the RANKX() function as a measure?

[E12345]

When I use the same DAX I used in a column, but try to make a measure, it does not work. This is the error message I get:

A single value for column 'Date Presented' in table 'My Table' 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.

[Ashish_Mathur]

Hi,

Could you share the download link of the PBI file?

[E12345]

Unfortunately I do not have the permissions to share PBIX (only super users can - I already inquired)

However, I can paste the dataset I have below - see if you can copy it

into Excel and upload it.

Date Presented	Presenter
1/1/2023	Joe
3/20/2023	Mary
5/30/2023	Linda
6/20/2023	Joe
7/1/2023	Steve
10/18/2023	Garry
11/25/2023	Mary
12/15/2023	Linda
1/14/2024	Pete
2/25/2024	Joe
3/30/2024	Linda
5/14/2024	Mary
6/15/2024	Luke

[Ashish_Mathur]

Hi,

PBI file attached.

[E12345]

Hi Ashish,

Thank you so much for trying to solve this problem! And you are a Super User, so you can attach PBIX! 

I looked at your solution where you use date table (so clever!) to manupulate the sorting order.
It works perfectly well when I make individual selections from the slicer (thanks!),

However, when I "unclick" all my slicer selections so that "All" are selected (or none are selected, rather), the total sort order gets broken - I njeed the sort order to always by by "Date of Presentation" (earliest to latest). Pasing below my result. Is there anythign that can be done for the sort order to be correct and by Presented Date every time, even when no slicers are selected? Can you please see if you can figure out to have this one resolved as well. Thanks so much! I learned a lot just by looking at how to use the date table, I appreciate it. 

Please see my screenshot below, which shows the sorting order issue (sort order has to be by Date from earliest to latest, in all instances, so here, too, I need to see all presnetations sorted by date and for the Measure column to show as 1, 2, 3, 4, 5, 6... until 13):

[Ashish_Mathur]

Hi,

I have not been able to solve the problem entirely.  File with my attempts is attached.

Hope this helps.

[E12345]

Thank you so mych for attempting. I appreciate it. I will keep this post open for now, in case someone else can take a crack at it and perhaps solve it (or maybe Microsoft will see this post and come up with some sort of special column that would dynamically adjust ;)) 
I think there is a web site to offer suggestions to Microsoft team for the improvements you need. I might find it and add my request for such a column. 

[Ashish_Mathur]

Hi,

This measure works

Measure = ROWNUMBER(ALLSELECTED(Data),ORDERBY(Data[Date Presented]))

Hope this helps.

[E12345]

Hmm. That line did not work for me at all ;(. It gave a "circular reference" error message. 
Do you mind sharing your PBIX (if possible?). 

[Ashish_Mathur]

I do not have the PBI file.  Please try again.

[E12345]

No problem, I just figured it out myself 😉
Below is the DAX measure that worked (I posted a reply with pictures, and somehow it shows up higher than this thread)... 

Running Total =

CALCULATE(

    COUNTROWS('My table'),

    FILTER(

        ALLSELECTED('My table'),

        'My table'[Date Presented] <= MAX('My table'[Date Presented])

    )

)