Dynamically change rank based on filters that are selected and for every ticket

[Sushmach109]

Hi Team,

 For every serviceID there are impacted ticket Ids and Date on which impacted.

Now I have to rank the order for each serviceId and according to date i need to sort

I did this and got the order using below formula.

Order = RANKX(FILTER('Impacted services','Impacted services'[ServiceID]=EARLIER('Impacted services'[ServiceID])),'Impacted services'[Date],,ASC,Dense)

But now the challenge is when i am using Date filters the order should change dynamically which is not happening.

Sample Data:

ServiceID  TicketID  Date                 Order

A                11            12/4/2018        2

A                21            11/4/2018        1

A                31           16/4/2018         3

B                41            13/4/2018        1

B                52            14/4/2018        2

B                61             18/4/2018       4

Now when i am selecting Date filters between 15/4/2018 and 20/4/2018

I am getting below data:

ServiceID  TicketID  Date                 Order

A                31           16/4/2018         3

B                61             18/4/2018       4

B                53             17/4/2018       3

But my expected output should be:

ServiceID  TicketID  Date                 Order

A                31           16/4/2018         1

B                61             18/4/2018       2

B                53             17/4/2018       1

Please help me in this.

I am strugging with this from last 1 week.

[v-yuezhe-msft]

@Sushmach109,

Please right click your table, select "New measure", then apply the DAX below.

order = RANKX(FILTER(ALLSELECTED('Impacted services'),'Impacted services'[ServiceID]=MAX('Impacted services'[ServiceID])),CALCULATE(MAX('Impacted services'[Date])),,ASC)

Regards,
Lydia

[Sushmach109]

I have tried measure.But this is working for only small data and for large data it is giving error and sometimes it keeps on buffering and never gives result.
It will be very helpful if there is any dax query to add in column which is similar to this instead of measure.

[v-yuezhe-msft]

@Sushmach109,

Calculate column doesn't response to slicer selection, please take a look at this KB to get more details.

How many rows in your table? And what error do you get when using measure?

Regards,
Lydia

[Sushmach109]

[v-yuezhe-msft]

@Sushmach109，

What is your data source? And how many rows in your table?

Regards,
Lydia

[Sushmach109]

SQL server database tables.Data is in lakhs.

No Problem is not solved.:(

[v-yuezhe-msft]

@Sushmach109,

How many rows in your table? Do you remove unnecessary rows from your table?

Regards,
Lydia