cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MMartin
Frequent Visitor

Dynamically indexing using DAX

Hi!

 

I have the next challenge ahead, but I have not been able to solve. I tell you, I want to have a dynamic index that only depends on the records shown in a table. I give you an example:

 

I have the next table with Countries and cities:

 

CountryCity
SpainBarcelona
SpainBilbao
UKLiverpool
UKLondon
SpainMadrid
UKManchester

 

What I would like to have is a measure  which allows creating the following index "#" (I have tried it with a calculated column, but when it is calculated in processing time and not in execution time, it is not possible):

 

#CountryCity
1SpainBarcelona
2SpainBilbao
3UKLiverpool
4UKLondon
5SpainMadrid
6UKManchester

 

In the event that we filter by "Spain"  it should show the following:

 

#CountryCity
1SpainBarcelona
2SpainBilbao
3SpainMadrid

 

If we look at the first table we have "City = Madrid" with a value in the measure "#" of 5, in the second table the value taken by the "#" is 3, which is correct, since it occupies the position 3 of the table.

 

In principle, you don't want this index to be conditioned by anything, which I think Rank functions aren't valid.

 

Does anyone dare to help me? Is it possible to do?

 

Thanks!

Best Regards

 

 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@MMartin , Sequence no in visual table is not supported yet

I logged an idea for that

 

Serial No in Visual Table
https://ideas.powerbi.com/ideas/idea/?ideaid=80612ec5-14a9-4929-a2ee-531943fa51d4

 

As of now I can think of measure

count = countx(filter(Table,[City]<=max(Table[City])),Table[City])

View solution in original post

1 REPLY 1
amitchandak
Super User
Super User

@MMartin , Sequence no in visual table is not supported yet

I logged an idea for that

 

Serial No in Visual Table
https://ideas.powerbi.com/ideas/idea/?ideaid=80612ec5-14a9-4929-a2ee-531943fa51d4

 

As of now I can think of measure

count = countx(filter(Table,[City]<=max(Table[City])),Table[City])

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Top Solution Authors