cancel
Showing results for 
Search instead for 
Did you mean: 
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

Accepted Solutions
Community Support Team
Community Support Team

Re: make code simple

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
jthomson New Contributor
New Contributor

Re: make code simple

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

Re: make code simple

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

AkhilAshok Established Member
Established Member

Re: make code simple

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.

Community Support Team
Community Support Team

Re: make code simple

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

Anonymous
Not applicable

Re: make code simple

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.

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)