cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
simondpalmer
Helper II
Helper II

Create a color value per Distinct values from a collection

Hi, I have a collection called "ListItems" with a nested collection called "Phase" that I want to give a color per Distinct "Phase" value.

I am collecting a List from a sharepoint site like follows:

 

ClearCollect(ListItems,'Project Schedule')

 

Capture01.JPG

 

Capture02.JPG

 

 

 

 

 

 

 

 

 

I am Getting the Distinct values with the following applied to the gallery item in the top left of the below image:

 

Distinct(ListItems, Phase.Value)

 

But I want to take it a step further by applying a ColorValue to each of these values.

Ideally I want to add it to the collection "ListItems" for each unique ListItems.Phase value. Because I want to apply this to a Gantt Chart for all items that are under that phase. (Gantt Chart is below showing what I want to achieve with colors for each phase):

Gantt-color.jpg

 Any help would be appreciated. This is based on what I have learnt from @Mr-Dang-MSFT 's Gantt Chart idea.

 

-EDIT- I want to cycle through a list of predefined RGB colors shown below. If it gets to the end of the list it just starts from the beginning again.

[{0, 127, 175},{95, 75, 139},{255, 176, 37},{215, 45, 92},{255, 121, 19},{183, 108, 164},{214, 180, 180},{196, 143, 101},

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
simondpalmer
Helper II
Helper II

I figured it out in the end (walking away and coming back with a fresh perspective helps..). After creating the Color table.

ClearCollect(Colors,{ColorIndex:0,Color:RGBA(0,127,175,1)},{ColorIndex:1,Color:RGBA(95,75,139,1)},{ColorIndex:2,Color:RGBA(255,176,37,1)},{ColorIndex:3,Color:RGBA(215,45,92,1)},{ColorIndex:4,Color:RGBA(255,121,19,1)},{ColorIndex:5,Color:RGBA(183,108,164,1)},{ColorIndex:6,Color:RGBA(214,180,180,1)},{ColorIndex:7,Color:RGBA(196,143,101,1)});

 For clarity, I created a Table that combined "Color" by "Phases" by an Index comparison or "LookUp".

ForAll(Distinct(ListItems, Phase.Value), Collect(DistinctPhases, {Value: Result, PhaseIndex: CountRows(DistinctPhases)}));
ClearCollect(IndexedPhases, AddColumns(DistinctPhases,"PhaseColor", LookUp(Colors, PhaseIndex=ColorIndex, Color)));

 Then used "AddColumns" to the embedded Gallery "GalleryTasksGantt" I originally had issues with.

AddColumns(IndexedPhases, "col",LookUp(IndexedPhases, Value=(ThisItem.Phase.Value),PhaseColor))

 

View solution in original post

7 REPLIES 7
ZabiBabar
Continued Contributor
Continued Contributor

@simondpalmer You should rewrite Colors like this and assign a color an index.

 

 

 

 

ClearCollect(Colors,{ColorIndex:0,Color:RGBA(0,127,175,1)},{ColorIndex:1,Color:RGBA(95,75,139,1)},{ColorIndex:2,Color:RGBA(255,176,37,1)},{ColorIndex:3,Color:RGBA(215,45,92,1)},{ColorIndex:4,Color:RGBA(255,121,19,1)},{ColorIndex:5,Color:RGBA(183,108,164,1)},{ColorIndex:6,Color:RGBA(214,180,180,1)},{ColorIndex:7,Color:RGBA(196,143,101,1)}); ClearCollect(Phase, Distinct(ListItems, Phase.Value))

 

 

 

 

And then you can create an indexed list of distinct phases

 

 

ClearCollect(DistinctPhases, Distinct(ListItems, Phase.Value));
ForAll(DistinctPhases, Collect(IndexedPhases, {Value: Result, PhaseIndex: CountRows(IndexedPhases)});

 

 

 

And finally, you can use the formula for the items property of the top left distinct phases gallery:

 

 

AddColumns(IndexedPhases, "Color", Lookup(Colors, ColorIndex=Mod(PhaseIndex, CountRows(Colors))))

 

 

 

Now you are free to use this collection as a lookup for your main gallery.

Lookup(IndexedPhases, Phase=ThisItem.Phase.Value, Color)

 

Best,
Zabi
Mark this post as a solution, or kudo it if you found it be helpful. You can choose more than one answer as solutions, including your own answer. 🙂

Thank you for your reply. So I put all of that together, amongst other irrelevant collections, as follows on home screen "On Visible":

ClearCollect(Colors,{ColorIndex:0,Color:RGBA(0,127,175,1)},{ColorIndex:1,Color:RGBA(95,75,139,1)},{ColorIndex:2,Color:RGBA(255,176,37,1)},{ColorIndex:3,Color:RGBA(215,45,92,1)},{ColorIndex:4,Color:RGBA(255,121,19,1)},{ColorIndex:5,Color:RGBA(183,108,164,1)},{ColorIndex:6,Color:RGBA(214,180,180,1)},{ColorIndex:7,Color:RGBA(196,143,101,1)});
ClearCollect(ProjectFinancials, Blank());
ClearCollect(WIPMonth, PA_WIP_GetMonth.Run());
ClearCollect(ListItems,'Project Schedule');
ClearCollect(DistinctPhases, Distinct(ListItems, Phase.Value));
ForAll(DistinctPhases, Collect(IndexedPhases, {Value: Result, PhaseIndex: CountRows(IndexedPhases)}));

Which works fine. 

Then for the gallery at the top left I use the following:

AddColumns(IndexedPhases, "Color", LookUp(Colors, Color=Mod(ColorIndex, CountRows(Colors))))

 

This comes up with an error message that "Color" is not a relevant color when used as "Fill" for an item inside the gallery:

ThisItem.Color

What have I done wrong? @ZabiBabar 

And how could I use this "Lookup" in my other Gallery which is pulling data from the original ListItems collection. That has the phase under ListItem.Phase.

The collections I have are as follows:

Capture-schedule01.JPGCapture-schedule02.JPGCapture-schedule03.JPG

ListItemsListItems

ZabiBabar
Continued Contributor
Continued Contributor

@simondpalmer  I believe you made a mistake here:

AddColumns(IndexedPhases, "Color", LookUp(Colors, Color=Mod(ColorIndex, CountRows(Colors))))

It should be:

AddColumns(IndexedPhases, Lookup(Colors, ColorIndex=Mod(PhaseIndex, CountRows(Colors))))

 

And then in your other gallery, you can do 

Lookup(IndexedPhases, Phase=ThisItem.Phase.Value, Color)

 

Best,
Zabi
Mark this post as a solution, or kudo it if you found it be helpful. You can choose more than one answer as solutions, including your own answer. 🙂

Thanks for the reply,It still doesn't work as it is expecting 3 arguments and has only received 2.

AddColumns(IndexedPhases, LookUp(Colors, ColorIndex=Mod(PhaseIndex, CountRows(Colors))))

I guess that is why I added "Colors" as a <column name>

ZabiBabar
Continued Contributor
Continued Contributor

Sorry, I made a mistake while correcting yours:

AddColumns(IndexedPhases, "Color", Lookup(Colors, ColorIndex=Mod(PhaseIndex, CountRows(Colors))))

 

The mistake you made was in this part of the formula:

LookUp(Colors, Color=Mod(ColorIndex, CountRows(Colors))

 It should be PhaseIndex instead of ColorIndex. Everything else is correct.

 

Best,
Zabi
Mark this post as a solution, or kudo it if you found it be helpful. You can choose more than multiple answers as solutions, including your own answer. 🙂

But you were right in adding the "Colors" column as a second argument. I am going to edit the original post to highlight that.

Thank you so much that worked for the top left Gallery I just had to add for the "Fill" to be

ThisItem.Color.Color

After the "Items" for the gallery is set to 

AddColumns(IndexedPhases, "Color", LookUp(Colors, ColorIndex=Mod(PhaseIndex, CountRows(Colors))))

----------

However, the main Gallery (GalleryTasks) below circled in red I am still having difficulty as it has an embedded horizontal gallery (GalleryTasksGantt) circled in blue to create the blue bars shown below

 

Capture.JPG

 

 As you can see I have the "Items" set for GalleryTasksGantt as

AddColumns(DateRange,"vis",Date>=(ThisItem.StartDate) && Date<=(ThisItem.DueDate))

(This provides a column called vis to turn on and off the blue bars as they correspond to a DateRange collection.)

The GalleryTasks gallery Items is set to

ListItems

How can I change the blue bar colors to match the Phase value for each of these Tasks?

simondpalmer
Helper II
Helper II

I figured it out in the end (walking away and coming back with a fresh perspective helps..). After creating the Color table.

ClearCollect(Colors,{ColorIndex:0,Color:RGBA(0,127,175,1)},{ColorIndex:1,Color:RGBA(95,75,139,1)},{ColorIndex:2,Color:RGBA(255,176,37,1)},{ColorIndex:3,Color:RGBA(215,45,92,1)},{ColorIndex:4,Color:RGBA(255,121,19,1)},{ColorIndex:5,Color:RGBA(183,108,164,1)},{ColorIndex:6,Color:RGBA(214,180,180,1)},{ColorIndex:7,Color:RGBA(196,143,101,1)});

 For clarity, I created a Table that combined "Color" by "Phases" by an Index comparison or "LookUp".

ForAll(Distinct(ListItems, Phase.Value), Collect(DistinctPhases, {Value: Result, PhaseIndex: CountRows(DistinctPhases)}));
ClearCollect(IndexedPhases, AddColumns(DistinctPhases,"PhaseColor", LookUp(Colors, PhaseIndex=ColorIndex, Color)));

 Then used "AddColumns" to the embedded Gallery "GalleryTasksGantt" I originally had issues with.

AddColumns(IndexedPhases, "col",LookUp(IndexedPhases, Value=(ThisItem.Phase.Value),PhaseColor))

 

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power Apps Community Call Jan. 2022 768x460.png

Power Apps Community Call

Please join us on Wednesday, January 19th, at 8a PDT. Come and learn from our amazing speakers!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Top Solution Authors
Top Kudoed Authors
Users online (1,730)