Forum Discussion
Dynamic row numbers based on Rank Column
Hell All,
I have written a calculated column to get the ranks for each sbu's employee count.
Formula:-
Ranks =
RANKX(
FILTER('Table','Table'[Date]=EARLIER('Table'[Date])),
'Table'[Count],,,Dense)
Ouput:-
Now i would like to get the count of rows which changes dynamically based on rank column.
i.e
| SBU | Count | Ranks | RowNum |
| A | 33 | 1 | 1 |
| B | 9 | 2 | 2 |
| C | 5 | 3 | 3 |
| D | 3 | 4 | 4 |
| E | 3 | 4 | 5 |
| F | 2 | 5 | 6 |
| G | 2 | 5 | 7 |
| H | 1 | 6 | 8 |
| I | 1 | 6 | 9 |
| J | 1 | 6 | 10 |
Can anyone please suggest me for the same.
Thanks
Mohan V
Well i got a solution on my own..but its not by using DAX..
Its magic power query.Solution:-
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdFNCoAgEIbhu8xaMJ0iW1ba3xXE+18jUSonvp08DDO8GCPNpKjXVtvOuPycKKlIi0RTcJXIBb1EWzCgyQ3t3BEeEoeCJzp0/Q7VSzWJH3VNEqMklks9wiCxTWKUxCjpxbFJYpT0Yf6QdAM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [SBU = _t, Date = _t, Count = _t]), #"Changed Type1" = Table.TransformColumnTypes(Source,{{"Count", Int64.Type}}), #"Sorted Rows" = Table.Sort(#"Changed Type1",{{"Date", Order.Ascending}, {"Count", Order.Descending}}), #"Changed Type" = Table.TransformColumnTypes(#"Sorted Rows",{{"Count", Int64.Type}, {"Date", type date}}), Partition = Table.Group(#"Changed Type", {"Date"}, {{"Count", each Table.AddIndexColumn(_, "Index2",1,1), type table}}), #"Expanded Count" = Table.ExpandTableColumn(Partition, "Count", {"SBU", "Count", "Index2"}, {"SBU", "Count.1", "Index2"}), #"Changed Type2" = Table.TransformColumnTypes(#"Expanded Count",{{"SBU", type text}, {"Count.1", Int64.Type}, {"Index2", Int64.Type}}) in #"Changed Type2"Thanks for the help Anonymous
4 Replies
HI Anonymous,
Power bi not support create dynamic calculated column/table based slicer or filter. you need to use measure to instead.
Calculated Column/Table Change Dynamically According to Slicer Selection in the Report.
Regard,
Xiaoxin Sheng
Anonymous Thanks for the confirmation.
Well i think i need to go by a workaround solution for what i am looking for.
I ll give you detailed infor regarding the report that i am creating.
I hope and i wish you will come up with some suggestions or workaround solutions.Actually, i am creating report, which is having all the cards visual for each sbu.
if i have ten sbu's then ten cards visual for each sbu which shows the emp count.
Now i would like to show these cards by their ranks based on emp count, which are changing dynamically for each day for each sbu.
i.e the top rank sbu card will be first and then the second by putting this ranks calculated column in visual level filter.
But here as you know some of the sbu's having same no emp count, then i cant go with ranks.
So thats the reason i would like to have a column or measure which will be giving the index/rownum based on the ranks.
Expected output:-
SBU Count Ranks Index A 3 4 4 B 2 5 6 C 1 6 8 D 33 1 1 E 3 4 5 F 2 5 7 G 9 2 2 H 1 6 9 I 5 3 3 J 1 6 10 So here if you see, 4,5,6 ranks are getting repeated.
for those i would like to have a column or measure which will give me
Please help.
Mohan V
Well i got a solution on my own..but its not by using DAX..
Its magic power query.Solution:-
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdFNCoAgEIbhu8xaMJ0iW1ba3xXE+18jUSonvp08DDO8GCPNpKjXVtvOuPycKKlIi0RTcJXIBb1EWzCgyQ3t3BEeEoeCJzp0/Q7VSzWJH3VNEqMklks9wiCxTWKUxCjpxbFJYpT0Yf6QdAM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [SBU = _t, Date = _t, Count = _t]), #"Changed Type1" = Table.TransformColumnTypes(Source,{{"Count", Int64.Type}}), #"Sorted Rows" = Table.Sort(#"Changed Type1",{{"Date", Order.Ascending}, {"Count", Order.Descending}}), #"Changed Type" = Table.TransformColumnTypes(#"Sorted Rows",{{"Count", Int64.Type}, {"Date", type date}}), Partition = Table.Group(#"Changed Type", {"Date"}, {{"Count", each Table.AddIndexColumn(_, "Index2",1,1), type table}}), #"Expanded Count" = Table.ExpandTableColumn(Partition, "Count", {"SBU", "Count", "Index2"}, {"SBU", "Count.1", "Index2"}), #"Changed Type2" = Table.TransformColumnTypes(#"Expanded Count",{{"SBU", type text}, {"Count.1", Int64.Type}, {"Index2", Int64.Type}}) in #"Changed Type2"Thanks for the help Anonymous