make code simple

[Anonymous]

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)

Final =

IF([Index] =   1 || [Index] = 2 || [Index] = 3 || [Index] = 4 , 1 ,

IF([Index] =   5 || [Index] = 6 , 2,

IF([Index] =   7 || [Index] = 8 || [Index] = 9 || [Index] = 10 , 3 ,

IF([Index] = 11 || [Index] = 12 , 4 ,

IF([Index] = 13 || [Index] = 14 || [Index] = 15 || [Index] = 16 , 5 ,

IF([Index] = 17 || [Index] = 18 ,6 ,

IF([Index] = 19 || [Index] = 20 || [Index] = 21 || [Index] = 22 , 7 ,

IF([Index] = 23 || [Index] = 24 , 8 ,

IF([Index] = 25 || [Index] = 26 || [Index] = 27 || [Index] = 28 || [Index] = 29 , 9 ,

IF([Index] = 30 || [Index] = 31 || [Index] = 32 || [Index] = 33 || [Index] = 34 , 10 , 11

))))))))))

and the data set i am using

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

[v-lili6-msft · Accepted solution]

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

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

[Anonymous]

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.

[jthomson]

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

[Anonymous]

I think I get what your saying, if it would be alright, could you show me what it would look like , Im rather new to the coding in Power BI. 

thank you for the help.

 -Collin