Dynamic row numbers based on Rank Column

[Anonymous]

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

[v-shex-msft]

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]

@v-shex-msft 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

[Anonymous · Accepted solution]

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 @v-shex-msft

[Anonymous]

Can you please provide this solution in dax .. power query is quite complecated to understand.