Forum Discussion

kongyuancn's avatar
kongyuancn
Icon for Helper II rankHelper II
4 years ago
Solved

RANKX on Table Expression

Hi There,

 

I have a smiple table:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclSK1YGRTmDSGUbGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Data = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Data", type text}})
in
    #"Changed Type"

 

I created a rank measure like this:

RankOnTable = RANKX(ALL('Table'),CALCULATE(MAX('Table'[Data])),,ASC)

 

Then I draged the table as this:

It seems perfect.

 

Then I created another measure to try to use a table expression:

RankOnTableExp = 
VAR DistinctTable = VALUES('Table'[Data])
VAR TableWithRank = ADDCOLUMNS(
    DistinctTable,
    "RANK",RANKX(DistinctTable,CALCULATE(MAX('Table'[Data])),,ASC)
)
VAR CurrentData = CALCULATE(MAX('Table'[Data]))
VAR CurrentDataRank = CALCULATE(MAXX(TableWithRank,[RANK]))
RETURN
    CurrentDataRank

Then I get the table which looks like:

 

Can anyone tell me what is wrong?

 

Thanks in advance

  • Thank you lbendlin for your reply. But it doesn't work.

    Values doesn't accept allselected inside, so I changed to distinct:

     

    RankOnTableExp =
    VAR DistinctTable = DISTINCT(ALLSELECTED('Table'[Data]))
    VAR TableWithRank = ADDCOLUMNS(
    DistinctTable,
    "RANK",RANKX(DistinctTable,CALCULATE(MAX('Table'[Data])),,ASC)
    )
    VAR CurrentData = CALCULATE(MAX('Table'[Data]))
    VAR CurrentDataRank = CALCULATE(MAXX(TableWithRank,[RANK]))
    RETURN
    CurrentDataRank

     


    And the result is still wrong:

     

     

6 Replies

  • RankOnTableExp = 
    VAR DistinctTable = VALUES(ALLSELECTED('Table'[Data]))
    VAR TableWithRank = ADDCOLUMNS(
        DistinctTable,
        "RANK",RANKX(DistinctTable,CALCULATE(MAX('Table'[Data])),,ASC)
    )
    VAR CurrentData = CALCULATE(MAX('Table'[Data]))
    VAR CurrentDataRank = CALCULATE(MAXX(TableWithRank,[RANK]))
    RETURN
        CurrentDataRank
    • kongyuancn's avatar
      kongyuancn
      Icon for Helper II rankHelper II

      Thank you lbendlin for your reply. But it doesn't work.

      Values doesn't accept allselected inside, so I changed to distinct:

       

      RankOnTableExp =
      VAR DistinctTable = DISTINCT(ALLSELECTED('Table'[Data]))
      VAR TableWithRank = ADDCOLUMNS(
      DistinctTable,
      "RANK",RANKX(DistinctTable,CALCULATE(MAX('Table'[Data])),,ASC)
      )
      VAR CurrentData = CALCULATE(MAX('Table'[Data]))
      VAR CurrentDataRank = CALCULATE(MAXX(TableWithRank,[RANK]))
      RETURN
      CurrentDataRank

       


      And the result is still wrong:

       

       

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Icon for Community Champion rankCommunity Champion
    VAR CurrentDataRank = MAXX(FILTER(TableWithRank,'Table'[Data]=CurrentData),[RANK])