cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sushmach109 Regular Visitor
Regular Visitor

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

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.

 

7 REPLIES 7
Moderator v-yuezhe-msft
Moderator

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

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

1.PNG

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Sushmach109 Regular Visitor
Regular Visitor

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

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.
Moderator v-yuezhe-msft
Moderator

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

@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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Sushmach109 Regular Visitor
Regular Visitor

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

one.png

 

one.png

 

one.png

Moderator v-yuezhe-msft
Moderator

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

@Sushmach109

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

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Sushmach109 Regular Visitor
Regular Visitor

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

SQL server database tables.Data is in lakhs.

No Problem is not solved.:(

Moderator v-yuezhe-msft
Moderator

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

@Sushmach109,

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

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors