cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Anonymous
Not applicable

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 question is if there is a way to simplifly my code and then an easier way to set it up so it is automaticly applied to new data I add in. 

            I know its a lot and might be difficult but  I would really appriciate the help.

below is some pictures to  show what i have

 

this is the code for the new column

 line Seperation =
IF([TestCondition2] = "A" && [ID] = 2, 1,
IF([TestCondition2] = "C" && [ID] = 2, 2,
IF([TestCondition2] = "D" && [ID] = 2 , 3 ,
IF([TestCondition2] = "A" && [ID] = 4 , 4 ,
IF([TestCondition2] = "B" && [ID] = 4 , 5 ,
IF([TestCondition2] = "E" && [ID] = 4 , 6 ,
IF([TestCondition2] = "A" && [ID] = 5 , 7 ,
IF([TestCondition2] = "B" && [ID] = 5 , 8 ,
IF([TestCondition2] = "F" && [ID] = 5 , 9 ,
IF([TestCondition2] = "G" && [ID] = 5 , 10 ,-1
))))))))))

 

The data for chart

IDFrequencyRequirement levelTestCondition2line Seperation
2400100A1
2800100A1
21000100A1
2800702
21000702
22000702
21200300D3
21400300D3
4360100A4
4806100A4
42000n/aA4
4360100B5
4806100B5
4806n/aE6
42000n/aE6
41200300E6
41400300E6
41401300E6
42700300E6
43100300E6
5200150A7
53200150A7
5200150B8
5806150B8
5806150F9
5902150F9
5928150F9
5941150F9
51200150F9
51400150F9
51710150F9
51910150F9
52110150F9
52170150F9
52500150F9
52700150F9
51200150G10
51400150G10
52700150G10
53200150G10

 

Thanks for the help.

 

-Collin

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Resolver III
Resolver III

Re: simplify code

How about this:

 

Column = Table1[ID] & Table1[TestCondition2]
Column 2 = RANKX(Table1, Table1[Column], , ASC, Dense)

View solution in original post

5 REPLIES 5
Highlighted
Anonymous
Not applicable

Re: simplify code

Collin-

See if this is what you had in mind:

 

1) Created a new column that combined your ID and TestCondtion2 Columns.  I did this in Power Query, but could also do in a calculated column.  

 

2) Used the following caclulated column to get the "Line Seperation":

New Line Seperation = 
VAR CurrentIDTest = Table2[ID TestCondition2]
RETURN

CALCULATE(
    DISTINCTCOUNT( Table2[ID TestCondition2]),
        FILTER( 
            ALL ( TABLE2 ), 
                CurrentIDTest > Table2[ID TestCondition2]
        )
)

which I  believe matches your output you were looking for:

Line Separtion Final Table.png

Highlighted
Resolver III
Resolver III

Re: simplify code

How about this:

 

Column = Table1[ID] & Table1[TestCondition2]
Column 2 = RANKX(Table1, Table1[Column], , ASC, Dense)

View solution in original post

Highlighted
Super User III
Super User III

Re: simplify code

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Highlighted
Super User III
Super User III

Re: simplify code

@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"

Simplify.png

 

 

 

Try my new Power BI game Cross the River
Highlighted
Anonymous
Not applicable

Re: simplify code

Exactly what i needed , Thankyou. 

 

 your post after this was helpfull as well.

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors