March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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
Solved! Go to 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
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
@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
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
Can you please provide this solution in dax .. power query is quite complecated to understand.
User | Count |
---|---|
117 | |
77 | |
58 | |
52 | |
46 |
User | Count |
---|---|
171 | |
117 | |
63 | |
57 | |
51 |