Forum Discussion
kongyuancn
Helper II
4 years agoRANKX 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
CurrentDataRankThen 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
- lbendlin
Super User
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
Helper 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
Community Champion
VAR CurrentDataRank = MAXX(FILTER(TableWithRank,'Table'[Data]=CurrentData),[RANK])- kongyuancn
Helper II
- wdx223_Daniel
Community Champion