Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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)

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
 
 
IDFrequencyRequirementLevelTestCondition1TestCondition2TestCondition3IndexFinal
1170Level2AUnitOne11
115106Level2AUnitOne21
130106Level2AUnitOne31
140096Level2AUnitTwo41
11596Level2AUnitTwo52
13096Level2AUnitTwo62
1170Level2BUnitOne73
115106Level2BUnitOne83
130106Level2BUnitOne93
140096Level2BUnitTwo103
11596Level2BUnitTwo114
13096Level2BUnitTwo124
3170Level2AUnitOne135
315106Level2AUnitOne145
330106Level2AUnitOne155
340096Level2AUnitTwo165
31596Level2AUnitTwo176
33096Level2AUnitTwo186
3170Level2BUnitOne197
315106Level2BUnitOne207
330106Level2BUnitOne217
340096Level2BUnitTwo227
31596Level2BUnitTwo238
33096Level2BUnitTwo248
6190Level2AUnitOne259
6290Level2AUnitOne269
615106Level2AUnitOne279
660106Level2AUnitOne / UnitTwo289
6400100Level2AUnitTwo299
6190Level2BUnitOne3010
6290Level2BUnitOne3110
615106Level2BUnitOne3210
660106Level2BUnitOne / UnitTwo3310
6400100Level2BUnitTwo3410

 

 

thank you for the help.

     - Collin

1 ACCEPTED SOLUTION
v-lili6-msft
Community Support
Community 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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
v-lili6-msft
Community Support
Community 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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
AkhilAshok
Solution Sage
Solution Sage

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
Not applicable

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

Anonymous
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.