cancel
Showing results for
Did you mean:
Highlighted
Member

## 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

 ID Frequency Requirement level TestCondition2 line Seperation 2 400 100 A 1 2 800 100 A 1 2 1000 100 A 1 2 800 70 C 2 2 1000 70 C 2 2 2000 70 C 2 2 1200 300 D 3 2 1400 300 D 3 4 360 100 A 4 4 806 100 A 4 4 2000 n/a A 4 4 360 100 B 5 4 806 100 B 5 4 806 n/a E 6 4 2000 n/a E 6 4 1200 300 E 6 4 1400 300 E 6 4 1401 300 E 6 4 2700 300 E 6 4 3100 300 E 6 5 200 150 A 7 5 3200 150 A 7 5 200 150 B 8 5 806 150 B 8 5 806 150 F 9 5 902 150 F 9 5 928 150 F 9 5 941 150 F 9 5 1200 150 F 9 5 1400 150 F 9 5 1710 150 F 9 5 1910 150 F 9 5 2110 150 F 9 5 2170 150 F 9 5 2500 150 F 9 5 2700 150 F 9 5 1200 150 G 10 5 1400 150 G 10 5 2700 150 G 10 5 3200 150 G 10

Thanks for the help.

-Collin

1 ACCEPTED SOLUTION

Accepted Solutions
Member

## Re: simplify code

`Column = Table1[ID] & Table1[TestCondition2]`
`Column 2 = RANKX(Table1, Table1[Column], , ASC, Dense)`
5 REPLIES 5
Super User

## 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:

Member

## Re: simplify code

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

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

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

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
Super User

## Re: simplify code

@Collin

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),
#"Expanded AllRows" = Table.ExpandTableColumn(#"Added Index", "AllRows", {"ID", "Frequency", "Requirement level", "line Seperation"}, {"ID", "Frequency", "Requirement level", "line Seperation"})
in
#"Expanded AllRows"```

Member

## Re: simplify code

Exactly what i needed , Thankyou.

Announcements

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Power Platform Summit North America

Register by September 5 to save \$200

#### PBI Community Highlights

Check out what's new in the Power BI Community!

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 383 members 3,608 guests
Recent signins: