为什么我不能有一个没有重复值的排名索引

huangapple go评论58阅读模式
英文:

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(
    &#39;Table&#39;,
    &#39;Table&#39;[ID]= EARLIER(&#39;Table&#39;[ID]) &amp;&amp;
        &#39;Table&#39;[Date Time] &lt; EARLIER(&#39;Table&#39;[Date Time])
),&#39;Table&#39;[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(&#39;Table&#39;))

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(
    &#39;Table&#39;,
    &#39;Table&#39;[ID]= EARLIER(&#39;Table&#39;[ID]) &amp;&amp;
        &#39;Table&#39;[Date Time] &lt; EARLIER(&#39;Table&#39;[Date Time])
),&#39;Table&#39;[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 ), &#39;Table&#39;[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

huangapple
  • 本文由 发表于 2023年2月16日 02:30:49
  • 转载请务必保留本文链接:https://go.coder-hub.com/75464063.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定