cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
amelo
New Member

How to generate unique ID for each category using a measure.

I've been trying create a unique ID using a measure based on a category to allow filtration. For example I've the following table, and I would like to create a measure that for each "location" I would had a diferent ID (1,2,3,..) 

 

Tablename: sensors

ID Instalation ID Sensor ConfigFile Location
1 1 Pressure sensorF1.txt room1
2 1 Temperature sensorF2.txt room1
3 1 Pressure sensorF3.txt house
4 1 Temperature sensorF4.txt house
5 1 Humidity sensorF5.txt room2
6 1 Temperature sensorF6.txt room2
7 1 Humidity sensorF7.txt outside
8 1 Temperature sensorF8.txt outside
1 2 Pressure sensorF1.txt room1
2 2 Temperature sensorF2.txt room1
3 2 Pressure sensorF3.txt house
4 2 Temperature sensorF4.txt house
5 2 Humidity sensorF5.txt room2
6 2 Temperature sensorF6.txt room2
7 2 Humidity sensorF7.txt outside
8 2 Temperature sensorF8.txt outside

 

The output would be:

 

ID Instalation ID Sensor ConfigFile Location UniqueID
1 1 Pressure sensorF1.txt room1 1
2 1 Temperature sensorF2.txt room1 1
3 1 Pressure sensorF3.txt house 2
4 1 Temperature sensorF4.txt house 2
5 1 Humidity sensorF5.txt room2 3
6 1 Temperature sensorF6.txt room2 3
7 1 Humidity sensorF7.txt outside 4
8 1 Temperature sensorF8.txt outside 4

 

I've tried to use the measure bellow. but, the EARLIER does not work in the measure.

 

UniqueID = RANKX(
FILTER(
sensors,
sensors[Location] = EARLIER(sensors[Location])
),
SUM(sensors[Location])
)

 

 

Do anyone has any ideia how could I do something like that?

 

1 ACCEPTED SOLUTION
smpa01
Super User
Super User

@amelo  you can achieve that with following two measures

 

 

 

 

_maxID = MAX(t1[Location])
_rank = RANKX(ALL(t1),[_maxID],,ASC,Dense)

 

 

 

caps.PNG

 

and if you want the ranking to be partitioned by Installion ID

 

Measure = RANKX(FILTER(ALL(t1),t1[Instalation ID]=MAX(t1[Instalation ID])),CALCULATE(MAX(t1[Location])),,ASC,Dense)

 

casw.PNG

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


View solution in original post

4 REPLIES 4
smpa01
Super User
Super User

@amelo  you can achieve that with following two measures

 

 

 

 

_maxID = MAX(t1[Location])
_rank = RANKX(ALL(t1),[_maxID],,ASC,Dense)

 

 

 

caps.PNG

 

and if you want the ranking to be partitioned by Installion ID

 

Measure = RANKX(FILTER(ALL(t1),t1[Instalation ID]=MAX(t1[Instalation ID])),CALCULATE(MAX(t1[Location])),,ASC,Dense)

 

casw.PNG

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


View solution in original post

Thank you!! Works like a charm!!! 

Well it is neet trick!

parry2k
Super User
Super User

@amelo do you want a unique id between each Installation Id? The output you showed is only for Installation ID, not showing both the IDs, the solution depends on how you want it with different installation ids.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Oh thats because I'm filtering to produce a report for instalation. So, only one instalation will be tested at each time. 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!