Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
Solved! Go to Solution.
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:
VAR CurrentDataRank = MAXX(FILTER(TableWithRank,'Table'[Data]=CurrentData),[RANK])
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
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:
User | Count |
---|---|
52 | |
51 | |
20 | |
17 | |
16 |
User | Count |
---|---|
113 | |
45 | |
44 | |
28 | |
22 |