Power BI 通过两列进行分区排名

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

Power BI Rank partition by two columns

问题

I would like to rank Partition by two Columns I have a solution using Nested Filters but is there a more efficient solution?

Example

I Tried the below.

Rank =
VAR t = tstRank[Entity Code]
VAR t2 = tstRank[Currency Ledger]
VAR r3 =
RANKX (
FILTER ( FILTER ( tstRank, [Currency Ledger] = t2 ), [Entity Code] = t ),
tstRank[NetSales]
)
RETURN
r3

Which has given the correct solution but is there a better way to achieve outcome?

英文:

I would like to rank Partition by two Columns I have a solution using Nested Filters but is there a more efficient solution?

Example

I Tried the below.

Rank = 
VAR t = tstRank[Entity Code]
VAR t2 = tstRank[Currency Ledger]
VAR r3 =
RANKX (
    FILTER ( FILTER ( tstRank, [Currency Ledger] = t2 ), [Entity Code] = t ),
    tstRank[NetSales]
    )
RETURN
r3

Which has given the correct solution but is there a better way to achieve outcome?

答案1

得分: 1

我猜显而易见的是要 && 你的谓词以便进行一次表扫描,而不是嵌套迭代器:

Rank = 
VAR t = tstRank[Entity Code]
VAR t2 = tstRank[Currency Ledger]
RETURN
    RANKX (
        FILTER ( 
            tstRank, 
            [Currency Ledger] = t2
              && [Entity Code] = t 
        ),
        tstRank[NetSales]
    )
英文:

I guess the obvious is to && your predicates to have one table scan instead of a nested iterator:

Rank = 
VAR t = tstRank[Entity Code]
VAR t2 = tstRank[Currency Ledger]
RETURN
    RANKX (
        FILTER ( 
            tstRank, 
            [Currency Ledger] = t2
              && [Entity Code] = t 
        ),
        tstRank[NetSales]
    )

huangapple
  • 本文由 发表于 2023年2月23日 19:51:54
  • 转载请务必保留本文链接:https://go.coder-hub.com/75544465.html
匿名

发表评论

匿名网友

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

确定