Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

simplify code

Hello. I have this new column "line Seperation" coded to get me an increnmenting number that coresponds to a line  EX. three rows  if data might correspond to #1 and the next 5 rows to #2 . My main q...
  • ssugar's avatar
    ssugar
    7 years ago

    How about this:

     

    Column = Table1[ID] & Table1[TestCondition2]
    Column 2 = RANKX(Table1, Table1[Column], , ASC, Dense)
TomMartens's avatar
TomMartens
Super User
7 years ago

Hey,

 

your question to simplify your DAX and prepare for automation is not that simple to answer, but it seems that you already have the answer you are looking for ;-)

 

First, the DAX could be simplified by modifying your data model and adding a separate table that contains the ID and testcondition2 as columns then you could use LOOKUPVALUE to "pull" the value from this new table, instead of an evergrowing nested IF. But I think this would be just a smaller optimization, if ever possible.

 

If none of the already given answers matches your requirement please consider describing the underlying rule(s), that builds the nested IF...

From the data I derive the following:

  • the starting value for the first testcondition2 of id 4 is 4 because id 2 has three values for testcondition2
  • basically, the numbering of the testcondition2 for each id is determined by the alphabetical order of the values of testcondition2

Wondering how many different possible values for testcondition2 exist.

 

Regards,

Tom

Zubair_Muhammad's avatar
Zubair_Muhammad
Community Champion
7 years ago

Anonymous

 

Another way is to add an Index Column using Power Query. We can Group using TestCondition2 column (using GroupKind.Local)

then add an index column. It assigns a new index to every different TestCondition2 in sequence. and will work when you add new rows

 

See the attached file

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hdJLEoMgDAbgq3RYO1MSQHDZ9yEcF71A778soFKh+XWRTMZvEgM6jopVp6zWMVPOl1SpqZslQInP9pt8SrdTTNw2ScSYiDOZnO+p+pEVyaaqr/ezRYLugSxLfM7vP9qOu8Zw4jhJ5mmPGL38opqq0zZk94gAsYddhkRy84ZprFsvyRcxmLaSriIUWS7pQJ4xhiKDZiQckFgCQtvlGrKYPEEaIDHtkIfk4BrLNzw81yv/ifLBGqtGNmakmdMX", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Frequency = _t, #"Requirement level" = _t, TestCondition2 = _t, #"line Seperation" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Frequency", Int64.Type}, {"Requirement level", type text}, {"TestCondition2", type text}, {"line Seperation", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"TestCondition2"}, {{"AllRows", each _, type table}},GroupKind.Local),
    #"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 1, 1),
    #"Expanded AllRows" = Table.ExpandTableColumn(#"Added Index", "AllRows", {"ID", "Frequency", "Requirement level", "line Seperation"}, {"ID", "Frequency", "Requirement level", "line Seperation"})
in
    #"Expanded AllRows"