英文:
Why can't I have a rank index without duplicate values
问题
我使用以下代码创建了一个排名:
RANK = RANKX(FILTER(
'Table',
'Table'[ID] = EARLIER('Table'[ID]) &&
'Table'[Date Time] < EARLIER('Table'[Date Time])
), 'Table'[Date Time],,ASC, Skip)
在某种程度上它有效并且遵循了正确的顺序,但它给了我1,1,1,4而不是1,2,3,4。前三行的日期和时间相同。接下来我添加了一个具有随机值的列:
SupportingColumn = RANDBETWEEN(1, COUNTROWS('Table'))
我希望我仍然可以执行相同的分组,但通过某个索引对其进行排序,因为哪一行在另一行之前并不重要。然而,这完全打乱了我的位置。
RANK = RANKX(FILTER(
'Table',
'Table'[ID] = EARLIER('Table'[ID]) &&
'Table'[Date Time] < EARLIER('Table'[Date Time])
), 'Table'[SupportingColumn],,ASC, Skip)
这与之前的代码相同,但现在完全将值分散在各处。不确定如何处理这个问题。
英文:
I created a rank with this code
RANK = RANKX(FILTER(
'Table',
'Table'[ID]= EARLIER('Table'[ID]) &&
'Table'[Date Time] < EARLIER('Table'[Date Time])
),'Table'[Date Time],,ASC,Skip)
which to some extent worked and followed the proper order but it gave me 1,1,1,4 instead of 1,2,3,4. The Date and Time is the same for the first three rows. Next thing I did was add a column with random values.
SupportingColumn = RANDBETWEEN(1,COUNTROWS('Table'))
I was hoping that I could still do the same grouping but sort it by some index cause it doesn't matter which row is before the other. However it completely disorigented my position.
RANK = RANKX(FILTER(
'Table',
'Table'[ID]= EARLIER('Table'[ID]) &&
'Table'[Date Time] < EARLIER('Table'[Date Time])
),'Table'[SupportingColumn],,ASC,Skip)
same code as before but now completely placing the values all over the place. Not sure how to do this.
答案1
得分: 1
请查看以下翻译:
"Instead of sorting 'Table'[Date Time] directly sort by this calculated column:
Sort Column = DATEDIFF ( DATE ( 1970, 1, 1 ), 'Table'[Date Time], SECOND ) + RAND() / 10
Basically this converts 'Table'[Date Time] into Unix Seconds and then adds a random number between 0 and 0.1"
翻译后的内容保留了代码部分,并对其他文本进行了翻译。
英文:
Instead of sorting 'Table'[Date Time] directly sort by this calculated column:
Sort Column = DATEDIFF ( DATE ( 1970, 1, 1 ), 'Table'[Date Time], SECOND ) + RAND() / 10
Basically this converts 'Table'[Date Time] into Unix Seconds and then adds a random number between 0 and 0.1
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论