Create Dynamic Index Column/Measure using DAX

[Anonymous]

Hello All,

I have a table which contains the SBU's and their count of emplyees by date wise.

I have written a calculated column which has given me

the ranks of each SBU by RANKX funcion by using employee count .

Ranks = 
    RANKX(
        FILTER('Table','Table'[Date]=EARLIER('Table'[Date])),
        'Table'[Count],,,Dense)

* SBU names Intentionally coloerd as white.

Here 4, 5, 6 ranks, are repeated bcz those sbu's are having the same values.
Now i would like to have another column or measure which gives me 
Index column which changes dynamically

I tried this but no luck.

index = 
    CALCULATE(
        COUNT('Table'[SBU]), ALL('Table'), FILTER('Table', 'Table'[Date]<=EARLIER('Table'[Date])), FILTER('Table', 'Table'[SBU]=EARLIER('Table'[SBU])))

Can any one please suggest me.

Thanks 

Mohan V

[v-juanli-msft]

Hi @Anonymous

"Index column which changes dynamically"

How should Index column change dynamically?

Could you show me expected result you want or give a example.

Here I test with your dataset and formula

Best Regards

Maggie

[Anonymous]

Thanks for the reply @v-juanli-msft

the dynamic index column should be get values based on the rank column.

the expected output is,

SBU	Count	Ranks	Index
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

Here i would like to get the row numbers column/measure based on rank values.

If you see the rank columns, there 4, 5, 6 values are getting repeated as the count value is same.
So here i would like to get the row numbers index values.

Please suggest me.

[v-juanli-msft]

Hi @Anonymous

In query editor, create an index column from 1,

Then create a calculated column

dynamic index = CALCULATE(COUNT('Table'[Ranks]),FILTER(ALL('Table'),[Date]=EARLIER([Date])&&[Index]<=EARLIER([Index])))

Best Regards

Maggie

[Anonymous]

@v-juanli-msft thanks for the reply.

Well i think i have provided some wrong inputs, as they are already in sorted manner, which is not in my case.

the values you see in screen shot that i have provided are actually not in sorted manner.

i did by ranks calculated column.

Base table:- 

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 Type" = Table.TransformColumnTypes(Source,{{"Count", Int64.Type}, {"Date", type date}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Date", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Index",{{"Index", Int64.Type}})
in
    #"Changed Type1"

In My base table if i sorted the date column in ascending manner and created index column, and

when i tried your dax i got the below output.

But my expected out is:- 

SBUDateCountIndex/Rownum

C	4/2/2018	3	4
E	4/2/2018	3	5
F	4/2/2018	1	9
G	4/2/2018	1	10
I	4/2/2018	2	7
J	4/2/2018	33	1
H	4/2/2018	5	3
D	4/2/2018	2	6
B	4/2/2018	1	8
A	4/2/2018	9	2

Please help.

MohanV

[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-juanli-msft