Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
mohanpal
Frequent Visitor

Create a dynamic index column based on Date Selection by user

Hi all,

 

I want to create a dynamic index column based on whatever date user select.

 

Look at the sample table below,

 

1. If the user select 2nd Jan from slicer, the index against it should be one.

2. If user select 3rd jan, then index should be 1 against 3rd Jan.

3. If user select multiple dates , for example: 3rd, 5th and 6th Jan then index should be 1 for 3rd jan, 2 for 5th Jan, 3 for 6th jan and so on.

 

I have tried using RANKX function but it's not giving me desired output. Any help is appreciated.

 

mohanpal_0-1660884301418.png

 

1 ACCEPTED SOLUTION
v-jingzhang
Community Support
Community Support

Hi @mohanpal 

 

In Power BI, the calculated column cannot be dynamic according to users' selection in the slicer/filter. Only measures can be dynamic. You can use the following measure. 

Rank = RANKX(ALLSELECTED('Date'[Date]),CALCULATE(MAX('Date'[Date])),,ASC,Dense)

vjingzhang_0-1661249289938.png

 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

View solution in original post

4 REPLIES 4
azaki
Frequent Visitor

To have a dynamic index follow below steps

1- Add Index column to your data set

azaki_0-1702282173276.png

2- Create Measure like this 

MsrDynamicIndex = SUMX(FILTER(ALLSELECTED('Table_Name'),[Index]<=MAXX('Table_Name',[Index])),CALCULATE(1))
 
Now the measure will be your Dynamic Index whatever filters you applied it will reset the index 🙂
 
 
pedroluccas
Frequent Visitor

Hello!
I have a similar problem.

 

I am working witha data series that relates a "result" (pH, mass, etc) to "sample code" and, in order to calculate my intersect, slope and R² I need an index starting with 1

But when I filter my data, I need that the first sample be reenumerated to 1. Ex: 

- First analysis: Sample 1 to 20 (1 = 1,  2 = 2, 3 = 3, ..., 20 samples)

- Second analysis: Sample 8 to 16 (8 = 1, 9 = 2, 10 = 3, ..., 16 = 9)

 

I tried your solutions but it didn't work. Maybe because my sample code is a text.

 

pedroluccas_0-1665068556033.png

The same way I want select samples of annother product, ex: B1AA/22 - B12AA/22, and, when I change the products the index restart with 1

 

I expalin with more details of the statistics on DAX on LINEAR REGRESSION - X-AXIS IN TEXT FORMAT - Microsoft Power BI Community

 

Any ideas? @v-jingzhang 

Thank you very much

 

v-jingzhang
Community Support
Community Support

Hi @mohanpal 

 

In Power BI, the calculated column cannot be dynamic according to users' selection in the slicer/filter. Only measures can be dynamic. You can use the following measure. 

Rank = RANKX(ALLSELECTED('Date'[Date]),CALCULATE(MAX('Date'[Date])),,ASC,Dense)

vjingzhang_0-1661249289938.png

 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

lbendlin
Super User
Super User

 dynamic ... column

That is not something you can do in Power BI.  Either you have a column, or you have something dynamic (ie a measure).  You cannot have both.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors