DAX - RANK函数

Question

我有一个排名代码从网络基础设施，并需要写一个类似的DAX或MDX.Both可以工作。但DAX会更有用。

=Rank([OrderCount];([Category1];[Category2];[Category3])) 

我已经在DAX中尝试了下面的代码，但它不是我需要的。

=RANK.EQ(table1[OrderCount];table1[OrderCount])

你能帮我写出类似的东西吗？

Grouping by Count. This is extra.

Answer 1

我不太擅长DAX，但MDX的方式如下：

with
Dynamic Set OrderedSet as
Order(
    NonEmptyCrossJoin(
        [Dim Product].[Subcategory Name].[Subcategory Name].Members,
        [Dim Product].[Category Name].[Category Name].Members
        [Measures].[Order Quantity],
        2
    ),
    [Measures].[Order Quantity],
    BDESC
)

Member [Measures].[Rank] as
Rank(

        ([Dim Product].[Subcategory Name].Currentmember, 
        [Dim Product].[Category Name].Currentmember),
        OrderedSet
)

select 
{[Measures].[Order Quantity],[Measures].[Rank]} on 0,
non empty OrderedSet on 1
from 
[Adventure Works DW2016CTP3]

DenseRank：

with
Dynamic Set OrderedSet as
Order(
    NonEmptyCrossJoin(
      [Dim Product].[Subcategory Name].[Subcategory Name].Members,
      [Dim Product].[Category Name].[Category Name].Members,
      [Measures].[Order Quantity],
       2
    ),
    [Measures].[Order Quantity],
    BDESC
)

Dynamic Set DenseOrderedSet as
Order(
    NonEmpty(
       OrderedSet,
       [Measures].[RankFirstMatch]
    ),
    [Measures].[Order Quantity],
    BDESC
)

Member [Measures].[Rank] as
Rank(
  ([Dim Product].[Subcategory Name].Currentmember,[Dim Product].[Category Name].CurrentMember),
  OrderedSet
)

Member [Measures].[RankFirstMatch] as
IIF(
    [Measures].[Order Quantity] 
    =
    (
        OrderedSet.Item([Measures].[Rank] -2),
        [Measures].[Order Quantity]
    ),
    NULL,
    [Measures].[Rank]
)

Member [Measures].[RankDenseSet] as
Rank(
  ([Dim Product].[Subcategory Name].Currentmember,[Dim Product].[Category Name].CurrentMember),
  DenseOrderedSet
)


Member [Measures].[DenseRank] as
IIF(
    [Measures].[RankDenseSet] = 0,
    (OrderedSet.Item([Measures].[Rank] -2),[Measures].[DenseRank]),
    [Measures].[RankDenseSet]
)


select {[Measures].[Order Quantity],[Measures].[Rank],[Measures].[RankFirstMatch],[Measures].[RankDenseSet],[Measures].[DenseRank]} on 0,
non empty OrderedSet on 1
from [Adventure Works DW2016CTP3]

Answer 2

以下是您问题的DAX度量：

​

Rank =
IF (
    HASONEVALUE ( YourTableName[ProductName] ),
    RANKX (
        ALL ( YourTableName ),
        CALCULATE ( SUM ( YourTableName[Order Quantity] ) ),
        ,
        ,
        DENSE
    )
)

​

IF (HASONEVALUE ( YourTableNameProductName )....部件将从显示%1中删除您的总计