Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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.
Solved! Go to Solution.
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)
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
To have a dynamic index follow below steps
1- Add Index column to your data set
2- Create Measure like this
This generally works although I am wondering how this happened, see how first row is ranked 7th
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.
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
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)
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
Hi,
Rank = RANKX(ALLSELECTED('Date'[Date]),CALCULATE(MAX('Date'[Date])),,ASC,Dense)
This formula is considering date only, how do we modify it to consider date and time too?
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.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
24 | |
13 | |
12 | |
11 | |
8 |
User | Count |
---|---|
43 | |
26 | |
16 | |
15 | |
12 |