Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Create Dynamic Index Column/Measure using DAX

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

 

General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    8 years ago

    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

5 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    8 years ago

    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's avatar
      Anonymous
      Not applicable
      8 years ago

      Thanks for the reply v-juanli-msft

       

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

      the expected output is,

      SBUCountRanksIndex
      A3311
      B922
      C533
      D344
      E345
      F256
      G257
      H168
      I169
      J1610

       

      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's avatar
        v-juanli-msft
        Community Support
        8 years ago

        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