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 usin...
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

v-juanli-msft's avatar
v-juanli-msft
Icon for Community Support rankCommunity 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
    Icon for Community Support rankCommunity 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

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      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

      C4/2/201834
      E4/2/201835
      F4/2/201819
      G4/2/2018110
      I4/2/201827
      J4/2/2018331
      H4/2/201853
      D4/2/201826
      B4/2/201818
      A4/2/201892

       

      Please help.

       

      MohanV

      • Anonymous's avatar
        Anonymous
        Not applicable
        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