Forum Discussion
Create index with unique values
I am trying to format data which i am extracting from our telephone system. Our telephone system writes an table rule for every step where a phone call goes through (incoming on the main connection, tranferring to the telephone menu, transfering to the department, transfering to an agent, etc.)
The data i want to see is the last step of the phone call which is always answering by an agent. I am trying to achieve this by creating an index so i can get the data of the highest index value, but i am stuck.
An example of my data:
| Date/Time | Call ID | Telephone number |
| 2020-08-11 12:13:47 | 151680 | +3161234567 |
| 2020-08-11 12:13:47 | 151680 | +3161234567 |
| 2020-08-11 12:13:49 | 151680 | +3161234567 |
| 2020-08-11 12:13:49 | 151680 | +3161234567 |
| 2020-08-11 12:14:02 | 151680 | +3161234567 |
| 2020-08-11 12:14:02 | 151680 | +3161234567 |
Normally i would do this by creating a customer index with a rankx based on call id and date / time like this:
RANKX (
FILTER ( 'Table', 'Table'[Call ID] = EARLIER ('Table'[Call ID] ) ),
'Table'[Date/Time],
,
ASC,
Dense
)
But as you might understand this gives double index values like 2 times 1, 2 times 2, 2 times 3, etc.
I am looking for a method to make an index with unique values, please advice.
Thank you in advance!
lgeraeds - Check out The Mythical DAX Index - https://community.powerbi.com/t5/Quick-Measures-Gallery/The-Mythical-DAX-Index/td-p/1093214 It handles ties flawlessly.
But, you should be doing it in Power Query but it looks like your situation you can't.
All that said, I don't understand why you can't just use Lookup Min/Max - https://community.powerbi.com/t5/Quick-Measures-Gallery/Lookup-Min-Max/m-p/985814#M434
3 Replies
- Greg_Deckler
Community Champion
lgeraeds - Check out The Mythical DAX Index - https://community.powerbi.com/t5/Quick-Measures-Gallery/The-Mythical-DAX-Index/td-p/1093214 It handles ties flawlessly.
But, you should be doing it in Power Query but it looks like your situation you can't.
All that said, I don't understand why you can't just use Lookup Min/Max - https://community.powerbi.com/t5/Quick-Measures-Gallery/Lookup-Min-Max/m-p/985814#M434
- amitchandak
Super User
lgeraeds , you have break ties
refer
https://databear.com/how-to-use-the-dax-rankx-function-in-power-bi/
For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/367415 - DataZoe
Microsoft Employee
lgeraeds You can do this in the "Transform Data" screen too. Add a step to sort the data by number, then by date, then add an index column.
You can see the above by pasting this into a blank query's advanced editor:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WstA3NNA3MjAyUNJRMjQyVorVwSlmCBMzMTXDI2YJEzK3sIQKmaOYFgsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Number = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Number", Int64.Type}}), #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Number", Order.Ascending}}), #"Changed Type1" = Table.TransformColumnTypes(#"Sorted Rows",{{"Date", type datetime}}), #"Sorted Rows1" = Table.Sort(#"Changed Type1",{{"Date", Order.Ascending}}), #"Added Index" = Table.AddIndexColumn(#"Sorted Rows1", "Index", 1, 1, Int64.Type) in #"Added Index"
