Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Good morning all,
I ask for your help with a measure.
I have a dataset with the following columns:
I would like to create an index based on the values of a sensor. The sensor values should be sorted in descending order. I managed to create the index with the RANKX function like in the following picture:
But unfortunately several sensor values are repeated and the index does not continue to increase. Clearly, I would like to do as in this Excel:
Thank you in advance for your help,
Have a good day
Joel
Solved! Go to Solution.
Hi @Anonymous
I notice that in your data, the rows with the same Sensor Value are sorted by dt ascendingly. So I will take dt into account to create a new sensor value in effect to be used in RANKX function.
Please refer to the following sample and try the DAX code to create an index column.
Index = RANKX('Demo','Demo'[SensorValue] - VALUE(Demo[DateTime]) * 0.00000001,,0,Skip)
Multiplying a very small number to DateTime value and taking it away from Sensor Value will create a decimal number a little smaller than original Sensor Value but not repeated. So it can be used in RANKX function to make the Index value not repeated.
Kindly let me know if this works.
Community Support Team _ Jing Zhang
If this post helps, please consider Accept it as the solution to help other members find it.
Thank you for your help @v-jingzhang ! I used an index in Power Query because I have a very large quantity of Data but thank you again !
Hi @Anonymous
I notice that in your data, the rows with the same Sensor Value are sorted by dt ascendingly. So I will take dt into account to create a new sensor value in effect to be used in RANKX function.
Please refer to the following sample and try the DAX code to create an index column.
Index = RANKX('Demo','Demo'[SensorValue] - VALUE(Demo[DateTime]) * 0.00000001,,0,Skip)
Multiplying a very small number to DateTime value and taking it away from Sensor Value will create a decimal number a little smaller than original Sensor Value but not repeated. So it can be used in RANKX function to make the Index value not repeated.
Kindly let me know if this works.
Community Support Team _ Jing Zhang
If this post helps, please consider Accept it as the solution to help other members find it.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |