Forum Discussion

kongyuancn's avatar
kongyuancn
Helper II
4 years ago

Measre in Matrix Column

Hi There,

I have simple table:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("NY45EsJAEMT+MrGDHd8OAYNP+MCW//8NNNAbqKrVkXI2t8pu4CnZVWWr2Xeo5Q37AY28Zc/Qyjv2Ezp5z35BLx/YCwzykb3CKJ/YG0xyT8j+D9ITiUc8pdEj8oynVHpkvuMpnR6hn3h+pdcX", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Name = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Name", type text}, {"Value", Int64.Type}})
in
    #"Changed Type"

It looks like as followings:

Then I made a matrix, which looks like:

 

Row is the Names and Column is the IDs. And I use a slicer to filter what ID I want.

Then I get a request: User want to use a simple index (1,2,3,4) on the column instead of ID. Whatever the IDs are, he want the index is always starts from 1.

Then I create a measure:

ID to Index = RANKX(ALLSELECTED('Table'), CALCULATE(MAX('Table'[ID])),,ASC)

It seems works well:

Now I want to use this measure instead of real ID. But it seems Matrix Columns doesn't support measures.

 

Please help to guide how to implement it.

3 Replies