Sensor value index measure DAX

[Anonymous]

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

[AlB]

@Anonymous 

I'm getting a bit lost. I thought all you needed was solved with the M part. It seems now that was actually unnecessary. This should be useful:

https://www.sqlbi.com/articles/rankx-on-multiple-columns-with-dax-and-power-bi/

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[AlB]

@Anonymous 

Here you go. Place the following M code in a blank query to see the steps.

let
    Source = Excel.CurrentWorkbook(),
    Table1 = Source{[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Table1,{{"id", Int64.Type}, {"dt", type datetime}, {"id_mesure_faucon", Int64.Type}, {"valeur", Int64.Type}, {"id_site", Int64.Type}, {"libelle_departement", type text}}),

    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"valeur", Order.Descending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1, Int64.Type)
in
    #"Added Index"

It simply sorts by "valuer" and then adds the index column. You might want to add a second column to sort by if you want to break the ties in a specific way. Change the Source and Table1 steps as necessary to get your source data

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Anonymous]

Hello @AlB  and thank you. 

I created the index as you advised me. Then, to be able to create my index on the values of my sensors I created a second index. I thought I could create it by a simple measure but the result was bad because I don't want duplicates in the indexes:

So I had to create an Index sensor value measure as below:

Then a Frequency sensor value measure :

Would a single measure have been sufficient in your opinion?

Thanks for your help,

cordially

[Anonymous]

https://1drv.ms/x/s!Ao1OrcTeY008gYUpjr6PF72-Qiwenw?e=Yjivlo 

Thanks for all, 

I think I succeed to share my file. This file includes the columns present in my Power Query of my company's Dataset for the day of 12/02/2020 (Yesterday).
The following image is a screenshot of the data section of my report:

cordially

[Anonymous]

Soory, this picture is a screenshot of the data section of my report:

[AlB]

@Anonymous 

You have to share the URL to the file hosted elsewhere: Dropbox, Onedrive... or just upload the file to a site like tinyupload.com (no sign-up required).

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[AlB]

@Anonymous 

Can you share some sample data, like what you show in the last pic above, but in text-tabular format instead of in a scree cap? So that it can be copied directly. Copy a fragment of the table in Excel and paste it here.

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Anonymous]

Hello @AlB and thank you again for your help

I can't seem to attach a sample of my file. The formats are not accepted:

do you want me to send it to you by email?

cordially

[AlB]

Hi @Anonymous 

You could do this best, and very easily, in Power Query. Just sort by Sensor values and then add an index column. You can also sort by sensor values and then date (the date column that appears on your last pic) if necessary

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Anonymous]

Hello @AlB  and thank you for your response.
The constraint that I have is that I cannot touch the structure of my Dataset because it is used by my whole company so the PowerQuery solution is not possible. On the other hand, your suggestion to sort by the sensor values ​​then to sort by date is interesting. Do you think a measurement could do that? If so which one please?

Thanks for your help and have a nice day