Forum Discussion

MMartin's avatar
MMartin
Frequent Visitor
5 years ago
Solved

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