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)),...
  • kongyuancn's avatar
    kongyuancn
    4 years ago

    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:

     

     

lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
4 years ago
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
    4 years ago

    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: