Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
@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
@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
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
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
Soory, this picture is a screenshot of the data section of my report:
@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
@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
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
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
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