Forum Discussion
make code simple
Hello, i have data that im using to display some lines on a line graph. In order for them to display correctly i need to force the data points to group in a certin way through the "Final" column. for example if the first three rows are all one line, i have a collumn that lables them as #1 next three rows are #2 , and so on.
My main question is there a way to simplify this code and / or beable to code it in a way that i works when I add new data as well. any and all help is greatly appriciated.
here is my code now for column "Final" (sorta Hardcoded this out)
| ID | Frequency | RequirementLevel | TestCondition1 | TestCondition2 | TestCondition3 | Index | Final |
| 1 | 1 | 70 | Level2 | A | UnitOne | 1 | 1 |
| 1 | 15 | 106 | Level2 | A | UnitOne | 2 | 1 |
| 1 | 30 | 106 | Level2 | A | UnitOne | 3 | 1 |
| 1 | 400 | 96 | Level2 | A | UnitTwo | 4 | 1 |
| 1 | 15 | 96 | Level2 | A | UnitTwo | 5 | 2 |
| 1 | 30 | 96 | Level2 | A | UnitTwo | 6 | 2 |
| 1 | 1 | 70 | Level2 | B | UnitOne | 7 | 3 |
| 1 | 15 | 106 | Level2 | B | UnitOne | 8 | 3 |
| 1 | 30 | 106 | Level2 | B | UnitOne | 9 | 3 |
| 1 | 400 | 96 | Level2 | B | UnitTwo | 10 | 3 |
| 1 | 15 | 96 | Level2 | B | UnitTwo | 11 | 4 |
| 1 | 30 | 96 | Level2 | B | UnitTwo | 12 | 4 |
| 3 | 1 | 70 | Level2 | A | UnitOne | 13 | 5 |
| 3 | 15 | 106 | Level2 | A | UnitOne | 14 | 5 |
| 3 | 30 | 106 | Level2 | A | UnitOne | 15 | 5 |
| 3 | 400 | 96 | Level2 | A | UnitTwo | 16 | 5 |
| 3 | 15 | 96 | Level2 | A | UnitTwo | 17 | 6 |
| 3 | 30 | 96 | Level2 | A | UnitTwo | 18 | 6 |
| 3 | 1 | 70 | Level2 | B | UnitOne | 19 | 7 |
| 3 | 15 | 106 | Level2 | B | UnitOne | 20 | 7 |
| 3 | 30 | 106 | Level2 | B | UnitOne | 21 | 7 |
| 3 | 400 | 96 | Level2 | B | UnitTwo | 22 | 7 |
| 3 | 15 | 96 | Level2 | B | UnitTwo | 23 | 8 |
| 3 | 30 | 96 | Level2 | B | UnitTwo | 24 | 8 |
| 6 | 1 | 90 | Level2 | A | UnitOne | 25 | 9 |
| 6 | 2 | 90 | Level2 | A | UnitOne | 26 | 9 |
| 6 | 15 | 106 | Level2 | A | UnitOne | 27 | 9 |
| 6 | 60 | 106 | Level2 | A | UnitOne / UnitTwo | 28 | 9 |
| 6 | 400 | 100 | Level2 | A | UnitTwo | 29 | 9 |
| 6 | 1 | 90 | Level2 | B | UnitOne | 30 | 10 |
| 6 | 2 | 90 | Level2 | B | UnitOne | 31 | 10 |
| 6 | 15 | 106 | Level2 | B | UnitOne | 32 | 10 |
| 6 | 60 | 106 | Level2 | B | UnitOne / UnitTwo | 33 | 10 |
| 6 | 400 | 100 | Level2 | B | UnitTwo | 34 | 10 |
thank you for the help.
- Collin
hi, Anonymous
You may try this formula as below:
Column 2 = SWITCH(TRUE(), Table1[Index]>=1&&Table1[Index]<=4,1, Table1[Index]>=5&&Table1[Index]<=6,2, Table1[Index]>=7&&Table1[Index]<=10,3, Table1[Index]>=11&&Table1[Index]<=12,4, Table1[Index]>=13&&Table1[Index]<=16,5, Table1[Index]>=17&&Table1[Index]<=18,6, Table1[Index]>=19&&Table1[Index]<=22,7, Table1[Index]>=23&&Table1[Index]<=24,8, Table1[Index]>=25&&Table1[Index]<=29,9, Table1[Index]>=30&&Table1[Index]<=34,10, 11)
Best Regards,
Lin
5 Replies
v-lili6-msftCommunity Support
hi, Anonymous
You may try this formula as below:
Column 2 = SWITCH(TRUE(), Table1[Index]>=1&&Table1[Index]<=4,1, Table1[Index]>=5&&Table1[Index]<=6,2, Table1[Index]>=7&&Table1[Index]<=10,3, Table1[Index]>=11&&Table1[Index]<=12,4, Table1[Index]>=13&&Table1[Index]<=16,5, Table1[Index]>=17&&Table1[Index]<=18,6, Table1[Index]>=19&&Table1[Index]<=22,7, Table1[Index]>=23&&Table1[Index]<=24,8, Table1[Index]>=25&&Table1[Index]<=29,9, Table1[Index]>=30&&Table1[Index]<=34,10, 11)
Best Regards,
Lin
jthomsonSolution Sage
You could try using SWITCH, which I've seen some people use in this spot, you could try a lookup table, rather than listing every possible option you could go if <5, 1, if <7, 2, if <11, 3 etc...
- AkhilAshok
Well.. best option is to create a mapping table (using Enter Data feature or Excel Sharepoint or even in a DB) with 2 columns, i.e., Index & Group. Afterwards, you can just create a relationship between the Data[Index] & Mapping[Index].
This way you don't need to write any complex DAX code! In case of new data, you just update the mapping table to reflect new index & groups.
Hello AkhilAshok
could you explain alittle what exactly you are talking about, it sounds to make sence, Im not sure how to do what you suggest.
thanks for the help.
