cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Collin Member
Member

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, @Collin

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

Collin Member
Member

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, @Collin

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

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
Virtual Launch Event

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 BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 123 members 1,630 guests
Please welcome our newest community members: